Help sending outlook file attachments

P

plpl78

Board Regular
Joined
May 8, 2009
Messages
87
My version of outlook is 2007


I’m not sure if this can be done any faster but here is my problem. Let’s say I have 40 PDF files. 20 of them have to go to one email address but they all have to be sent in 20 separate emails.


They are all going to the same email address and they all have to say they come from the same email address.


All of the PDF files are in the same location.


Here are the steps I use right now to do this.


1. Right click on the PDF file
2. Select send to
3. Select mail recipient
4. When the new mail message opens I enter in the from address
5. Next I enter in the to address
6. Next I insert my signature
7. I send the email


I repeat those 7 steps for each of the 20 PDF files. I thought about making a template for this however, I need the subject of each email to be the name of the PDF file and that is why I do it the way I have been doing it.


Anyone know of a faster way to do this? Thanks for any help you can give me.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
I don't do much with Outlook VBA. This code was originally written by Ron de Bruin to be run from Excel, and I have adapted it to loop through a directory of PDF files, attaching each to a new message.
Put this code in a new Excel module (Alt+F11, Insert > Module, Paste, then Alt+Q to return to Excel). Adapt where indicated by the comments. Run by pressing Alt+F8 and double-clicking the macro name.

Code: 
Option Explicit

''========================================================================================
''This example adds a HTML signature to a HTML mail.
''Change the mail address and the name of the signature file in the code before you run it.
''This code is adapted from http://www.rondebruin.nl/mail/folder3/signature.htm (Example 2)
''Additional code by Denis Wright
''Note: the code was written to be run from Excel
''========================================================================================

Function GetBoiler(ByVal sFile As String) As String
'**** Kusleika
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function

Sub Mail_Outlook_With_Signature_Html()
' Don't forget to copy the function GetBoiler in the module.
' Working in Office 2000-2010
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim SigString As String
    Dim Signature As String
    Dim strPDF_Folder As String
    'declarations added by DW
    Dim strPath As String
    Dim strFile As String
    Dim i As Integer
    Const dirHome = "C:\Test\" 'the directory that holds the PDF files

    Set OutApp = CreateObject("Outlook.Application")
    
    'process each of the PDF files in the target directory,
    'attaching them to an email
    strPath = Dir$(dirHome & "*.pdf")
    Do While Len(strPath)
        'get the name of the PDF file, without the path,
        'to use in the Subject line
        For i = Len(strPath) To 1 Step -1
            If Mid(strPath, i, 1) = "\" Then
                strFile = Mid(strPath, i + 1)
                strFile = Left(strFile, Len(strFile) - 4)
            End If
        Next i
        
        'Ron de Bruin's code
        Set OutMail = OutApp.CreateItem(0)
        
        'modify the body to suit
        strbody = "<H3><B>Dear Customer</B></H3>" & _
                  "Please visit this website to download the new version.<br>" & _
                  "Let me know if you have problems.<br>" & _
                  "<A HREF=""http://www.rondebruin.nl/tips.htm"">Ron's Excel Page</A>" & _
                  "<br><br><B>Thank you</B>"
    
        'Use this SigString if you use Windows XP
        SigString = "C:\Documents and Settings\" & Environ("username") & _
                    "\Application Data\Microsoft\Signatures\Personal.htm"
        
        'Use the second SigString if you use Vista or win 7 as operating system
        'SigString = "C:\Users\" & Environ("username") & _
                    "\AppData\Roaming\Microsoft\Signatures\Personal
        
        If Dir(SigString) <> "" Then
            Signature = GetBoiler(SigString)
        Else
            Signature = ""
        End If
    
        On Error Resume Next
        With OutMail
            'all of these can be customised
            .To = "bugs@warnerbros.com" 'adjust to suit
            .CC = ""
            .BCC = ""
            .Subject = "Emailing report: " & strFile
            .HTMLBody = strbody & "<br><br>" & Signature
            'You can add files also like this
            .Attachments.Add (strPath)
            
            'If you use .Display the message will be created but you will need to send it.
            'this is useful if you want to check the emails before sending.
            'it will also prevent the email warning message being displayed.
            .Display   'or use .Send
        End With
    
        On Error GoTo 0
        Set OutMail = Nothing
        
        strPath = Dir$
    Loop
        
    Set OutApp = Nothing
End Sub

Denis
 
Upvote 0
I wasn't able to get the code working however this is a good thing.

Depending on the type of file (not type of file extension) the .PDF file might have to go to a different email address.

If one is a home loan and the other is a personal loan they would both go to different email address.

It looks like that code (i think) would send all the .PDF files to the same email address.

I found this code on another site.

Sub SendMail()
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "joe@whatever.com"
.cc = ""
.BCC = ""
.Subject = "Try Me " & Format(Date, "dd/mmm/yy")
.Body = ""
.attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Send
End With
On Error Goto 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

I think it would help out alot if i could get that working however i tried using the .From code above the .To code and it wouldn't insert the from address. Any ideas?

Also this code opens a new email where i would want it to run in the new email message i aalready have opened. Can this be done?
 
Upvote 0
Do you have a simple way to identify home and personal loans, for example in the name of the PDF? If so, it's easy enough to have an IF statement in the code that sends the file to one or another address.
If not, we can work with the alternative you found.

Denis
 
Upvote 0
SydneyGeek said:
Do you have a simple way to identify home and personal loans, for example in the name of the PDF? If so, it's easy enough to have an IF statement in the code that sends the file to one or another address.
If not, we can work with the alternative you found.

Denis
Click to expand...

Yes we name each PDF file so we know what type of loan it is. The only thing i worry about is it attaching a PDF file to the wrong email address.

At the end of each .PDF file we put what type of loan it is so if its a home loan, we would put (in caps) H LOAN.

The file also has the loan applicants name as well and that's why I'm worried about it attaching loans to the wrong addresses.
 
Upvote 0
If you always have H LOAN on the home loans, the code can easily be adjusted to take advantage of that. See below...

Note -- the code you posted is pretty similar in many respects. The reason I found and modified the example below is because of the signature that you mentioned.
Also, you will need to change the code to reflect the name of *your* signature, or it will cause an error.

Code: 
Option Explicit

''========================================================================================
''This example adds a HTML signature to a HTML mail.
''Change the mail address and the name of the signature file in the code before you run it.
''This code is adapted from http://www.rondebruin.nl/mail/folder3/signature.htm (Example 2)
''Additional code by Denis Wright
''Note: the code was written to be run from Excel
''========================================================================================

Function GetBoiler(ByVal sFile As String) As String
'**** Kusleika
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function

Sub Mail_Outlook_With_Signature_Html()
' Don't forget to copy the function GetBoiler in the module.
' Working in Office 2000-2010
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim SigString As String
    Dim Signature As String
    Dim strPDF_Folder As String
    'declarations added by DW
    Dim strPath As String
    Dim strFile As String
    Dim i As Integer
    Const dirHome = "C:\Test\" 'the directory that holds the PDF files

    Set OutApp = CreateObject("Outlook.Application")
    
    'process each of the PDF files in the target directory,
    'attaching them to an email
    strPath = Dir$(dirHome & "*.pdf")
    Do While Len(strPath)
        'get the name of the PDF file, without the path,
        'to use in the Subject line
        For i = Len(strPath) To 1 Step -1
            If Mid(strPath, i, 1) = "\" Then
                strFile = Mid(strPath, i + 1)
                strFile = Left(strFile, Len(strFile) - 4)
            End If
        Next i
        
        'Ron de Bruin's code
        Set OutMail = OutApp.CreateItem(0)
        
        'modify the body to suit
        strbody = "<H3><B>Dear Customer</B></H3>" & _
                  "Please visit this website to download the new version.<br>" & _
                  "Let me know if you have problems.<br>" & _
                  "<A HREF=""http://www.rondebruin.nl/tips.htm"">Ron's Excel Page</A>" & _
                  "<br><br><B>Thank you</B>"
    
        'Use this SigString if you use Windows XP
        SigString = "C:\Documents and Settings\" & Environ("username") & _
                    "\Application Data\Microsoft\Signatures\Personal.htm"
        
        'Use the second SigString if you use Vista or win 7 as operating system
        'SigString = "C:\Users\" & Environ("username") & _
                    "\AppData\Roaming\Microsoft\Signatures\Personal
        
        If Dir(SigString) <> "" Then
            Signature = GetBoiler(SigString)
        Else
            Signature = ""
        End If
    
        On Error Resume Next
        With OutMail
            'all of these can be customised
            If InStr(1, strFile, "H LOAN") > 0 Then 'home loan
                .To = "bugs@warnerbros.com" 'address for home loans
            Else
                .To = "efudd@warnerbros.com" 'address for personal loans
            End If
            .CC = ""
            .BCC = ""
            .Subject = "Emailing report: " & strFile
            .HTMLBody = strbody & "<br><br>" & Signature
            'You can add files also like this
            .Attachments.Add (strPath)
            
            'If you use .Display the message will be created but you will need to send it.
            'this is useful if you want to check the emails before sending.
            'it will also prevent the email warning message being displayed.
            .Display   'or use .Send
        End With
    
        On Error GoTo 0
        Set OutMail = Nothing
        
        strPath = Dir$
    Loop
        
    Set OutApp = Nothing
End Sub

Denis
 
Last edited:
Upvote 0
SydneyGeek said:
If you always have H LOAN on the home loans, the code can easily be adjusted to take advantage of that. See below...

Note -- the code you posted is pretty similar in many respects. The reason I found and modified the example below is because of the signature that you mentioned.
Also, you will need to change the code to reflect the name of *your* signature, or it will cause an error.

Code: 
Option Explicit

''========================================================================================
''This example adds a HTML signature to a HTML mail.
''Change the mail address and the name of the signature file in the code before you run it.
''This code is adapted from http://www.rondebruin.nl/mail/folder3/signature.htm (Example 2)
''Additional code by Denis Wright
''Note: the code was written to be run from Excel
''========================================================================================

Function GetBoiler(ByVal sFile As String) As String
'**** Kusleika
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function

Sub Mail_Outlook_With_Signature_Html()
' Don't forget to copy the function GetBoiler in the module.
' Working in Office 2000-2010
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim SigString As String
    Dim Signature As String
    Dim strPDF_Folder As String
    'declarations added by DW
    Dim strPath As String
    Dim strFile As String
    Dim i As Integer
    Const dirHome = "C:\Test\" 'the directory that holds the PDF files

    Set OutApp = CreateObject("Outlook.Application")
    
    'process each of the PDF files in the target directory,
    'attaching them to an email
    strPath = Dir$(dirHome & "*.pdf")
    Do While Len(strPath)
        'get the name of the PDF file, without the path,
        'to use in the Subject line
        For i = Len(strPath) To 1 Step -1
            If Mid(strPath, i, 1) = "\" Then
                strFile = Mid(strPath, i + 1)
                strFile = Left(strFile, Len(strFile) - 4)
            End If
        Next i
        
        'Ron de Bruin's code
        Set OutMail = OutApp.CreateItem(0)
        
        'modify the body to suit
        strbody = "<H3><B>Dear Customer</B></H3>" & _
                  "Please visit this website to download the new version.<br>" & _
                  "Let me know if you have problems.<br>" & _
                  "<A HREF=""http://www.rondebruin.nl/tips.htm"">Ron's Excel Page</A>" & _
                  "<br><br><B>Thank you</B>"
    
        'Use this SigString if you use Windows XP
        SigString = "C:\Documents and Settings\" & Environ("username") & _
                    "\Application Data\Microsoft\Signatures\Personal.htm"
        
        'Use the second SigString if you use Vista or win 7 as operating system
        'SigString = "C:\Users\" & Environ("username") & _
                    "\AppData\Roaming\Microsoft\Signatures\Personal
        
        If Dir(SigString) <> "" Then
            Signature = GetBoiler(SigString)
        Else
            Signature = ""
        End If
    
        On Error Resume Next
        With OutMail
            'all of these can be customised
            If InStr(1, strFile, "H LOAN") > 0 Then 'home loan
                .To = "bugs@warnerbros.com" 'address for home loans
            Else
                .To = "efudd@warnerbros.com" 'address for personal loans
            End If
            .CC = ""
            .BCC = ""
            .Subject = "Emailing report: " & strFile
            .HTMLBody = strbody & "<br><br>" & Signature
            'You can add files also like this
            .Attachments.Add (strPath)
            
            'If you use .Display the message will be created but you will need to send it.
            'this is useful if you want to check the emails before sending.
            'it will also prevent the email warning message being displayed.
            .Display   'or use .Send
        End With
    
        On Error GoTo 0
        Set OutMail = Nothing
        
        strPath = Dir$
    Loop
        
    Set OutApp = Nothing
End Sub
Denis
Click to expand...

It's doing the same thing as last time when i run the macro nothing happens. I'm running it from excel 2007 like you told me to.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Joe4
  • Solved
Creating Microsoft Outlook Mail Rule with Wildcards
Replies
8
Views
118
Joe4
Joe4
J
  • Question Question
Create protected PDFs (with an Open password and/or a Permissions password) with Adobe PDF Services API
Replies
0
Views
78
John_w
J
G
  • Question Question
Modify Single Email in Outlook Drafts subfolder
Replies
0
Views
216
g_speran
G
P
  • Question Question
Outlook and OneDrive
Replies
4
Views
360
pincivma
P
A
  • Question Question
Outlook File Attachment using VBA
2 3
Replies
22
Views
3K
RoryA
RoryA

Forum statistics

Threads
1,224,521
Messages
6,179,286
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top