VBA help to send pdf attachments via Outlook to Excel distribution list

dbwiz

dbwiz

Active Member
Joined
Nov 20, 2007
Messages
275
I need help with VBA code to add to my Excel Distribution list that when run, would look for the path, grab the file that starts with the client name in Col A, and attach to the email, along with a predefined message/header.

The distribution list has the following:

Excel Workbook
ABCDE
1Client NameEmailFirst NameLast NamePath
2ABCJaneDoe@abc.comJaneDoeQ:\Agency Bill\PDF Invoices\2012 Installments\Jan 12
Sheet1
Excel 2007



I would want the email heading to be Installment Invoice and to say something like Dear First Name,
editable text (for example, "Attached please find a copy of your invoice dated"
last portion of path name ( in this case, Jan 12).

More editable text.
Insert Outlook signature.

I have been looking around and have found ways to generate and email pdfs from Excel - however our invoicing system generates bulk pdfs which we have to break up and save to file, in a path like the one above, and then manually select them one by one, typing in the email address, etc. We are hoping to automate the last step of this. Any help/tips would be greatly appreciated!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Hi,

I have put together this code.

It creates mails dependent on the existance of a path in column E.
If it finds more than one attachment in the same folder it will (hopefully) send another mail to the same customer.

You will need to comment out .Display and uncomment .Send once you are happy it's working. Personally I always use .Display so I can eyeball the mail before manually clicking Send. Your preference.

I haven't done the signature bit but here's a link that is useful:
http://www.rondebruin.nl/mail/folder3/signature.htm


Code: 
Sub Mail_Invoice()
Dim OutApp As Object
Dim OutMail As Object
    Set rng = Range(Range("E2"), Range("E" & Rows.Count).End(xlUp))
    For Each cell In rng
 
     Path = cell.Value
     If Path <> "" Then
 
     CliNme = cell.Offset(0, -4).Value
     SendName = cell.Offset(0, -3).Value
     FirstNme = cell.Offset(0, -2).Value
     Dte = Right(Path, Len(Path) - InStrRev(Path, "\"))
 
     ClientFile = Dir(Path & "\*.*")
 
     Do While ClientFile <> ""
 
     If InStr(ClientFile, CliNme) > 0 Then
 
     AttachFile = Path & "\" & ClientFile
 
     MailBody = Dear & FirstNme & vbNewLine & vbNewLine _
     & "Please find attached your invoice dated " & Dte _
     & vbNewLine & vbNewLine & [COLOR=red]"Add signature here"[/COLOR]
 
   Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(o)
        With OutMail
            .Subject = "Attached Invoice for " & Dte
            .To = SendName
            .Body = MailBody
            .Attachments.Add (AttachFile)
            .Display 
            '.Send
    End With
 
        Set OutMail = Nothing
        Set OutApp = Nothing
End If
ClientFile = Dir
Loop
End If
Next
End Sub
 
Last edited:
Upvote 0
Works great! Only needed to do a little tweaking and voila! Awesome, thank you!
 
Upvote 0
[TABLE="width: 1826"]
<tbody>[TR]
[TD] Cost Centre
[/TD]
[TD]Cost Centre Description
[/TD]
[TD]First Name
[/TD]
[TD]Surname
[/TD]
[TD]Email
[/TD]
[TD]Recipient 2
[/TD]
[TD]Recipient 3
[/TD]
[TD]Recipient 4
[/TD]
[TD]Recipient 5
[/TD]
[TD]Path
[/TD]
[/TR]
[TR]
[TD]IN75867
[/TD]
[TD]Off Shore Development team
[/TD]
[TD]Joe
[/TD]
[TD]Bloggs
[/TD]
[TD]jackreacher39uk@yahoo.com
[/TD]
[TD]jackreacher39uk@yahoo.com
[/TD]
[TD]jackreacher39uk@yahoo.com
[/TD]
[TD]jackreacher39uk@yahoo.com
[/TD]
[TD]jackreacher39uk@yahoo.com
[/TD]
[TD]C:\Users\SPACE\Desktop\Cost centre Reports\May 2013
[/TD]
[/TR]
[TR]
[TD]USA9262X
[/TD]
[TD]IT Central Team
[/TD]
[TD]Jim
[/TD]
[TD]Smith
[/TD]
[TD]jackreacher39uk@yahoo.com
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\SPACE\Desktop\Cost centre Reports\May 2013
[/TD]
[/TR]
[TR]
[TD]CH5679
[/TD]
[TD]China - Emerging Markets
[/TD]
[TD]John
[/TD]
[TD]Jones
[/TD]
[TD]jackreacher39uk@yahoo.com
[/TD]
[TD][/TD]
[TD]jackreacher39uk@yahoo.com
[/TD]
[TD][/TD]
[TD]jackreacher39uk@yahoo.com
[/TD]
[TD]C:\Users\SPACE\Desktop\Cost centre Reports\May 2013
[/TD]
[/TR]
[TR]
[TD]SW859512
[/TD]
[TD]Sweden Marketing and Comms
[/TD]
[TD]Valerie
[/TD]
[TD]Fernandez
[/TD]
[TD]jackreacher39uk@yahoo.com
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\SPACE\Desktop\Cost centre Reports\May 2013
[/TD]
[/TR]
[TR]
[TD]CAN0085
[/TD]
[TD]North America HR Division
[/TD]
[TD]Clare
[/TD]
[TD]Richards
[/TD]
[TD]jackreacher39uk@yahoo.com
[/TD]
[TD]jackreacher39uk@yahoo.com
[/TD]
[TD][/TD]
[TD]jackreacher39uk@yahoo.com
[/TD]
[TD][/TD]
[TD]C:\Users\SPACE\Desktop\Cost centre Reports\May 2013
[/TD]
[/TR]
[TR]
[TD]ES60045
[/TD]
[TD]Spain Software Development Team
[/TD]
[TD]Tom
[/TD]
[TD]Harrold
[/TD]
[TD]jackreacher39uk@yahoo.com
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C:\Users\SPACE\Desktop\Cost centre Reports\May 2013
[/TD]
[/TR]
[TR]
[TD]FR000091
[/TD]
[TD]France Marketing Division
[/TD]
[TD]Michael
[/TD]
[TD]Leigh
[/TD]
[TD]jackreacher39uk@yahoo.com
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]jackreacher39uk@yahoo.com
[/TD]
[TD]C:\Users\SPACE\Desktop\Cost centre Reports\May 2013
[/TD]
[/TR]
</tbody>[/TABLE]

Hi All, thanks for the above coding. I am trying to achieve something very similar, so thought this would be a great place to start.
I need help with VBA code to add to my Excel Distribution list that when run, would look for the path, grab the file that starts with the cost centre number (in Col A), and attach to the email and issue to a pre-defined distribution list.
The distribution list is shown above.
Email (column E) will populate in the To: field
Recipients (columns F to I) 2 to 5 in the CC: field
I would want the email heading to be Fixed Text: "Cost Centre report for - May 2013 (obviously this will change depending on the months report which is being created).
Within the Body of the text,
I would like it to say
Dear First name, (column C)
Please find attached a copy of your cost centre report for May 2013 (again month will be determined by the file)
Summary:
Cost centre: (Column A)
Cost centre Description (Column B)
Cost centre Manager: First name & Surname.

With thanks

Signature (TBC)
 
Upvote 0
Hi All, thanks for the above coding. I am trying to achieve something very similar, so thought this would be a great place to start.

I need help with VBA code to add to my Excel Distribution list that when run, would look for the path, grab the file that starts with the cost centre number (in Col A), and attach to the email and issue to a pre-defined distribution list.

The distribution list has the following:

[TABLE="width: 1826"]
<tbody>[TR]
[TD]Cost Centre[/TD]
[TD]Cost Centre Description[/TD]
[TD]First Name[/TD]
[TD]Surname[/TD]
[TD]Email[/TD]
[TD]Recipient 2[/TD]
[TD]Recipient 3[/TD]
[TD]Recipient 4[/TD]
[TD]Recipient 5[/TD]
[TD]Path[/TD]
[/TR]
[TR]
[TD]IN75867[/TD]
[TD]Off Shore Development team[/TD]
[TD]Joe[/TD]
[TD]Bloggs[/TD]
[TD]jackreacher39uk@yahoo.com[/TD]
[TD]jackreacher39uk@yahoo.com[/TD]
[TD]jackreacher39uk@yahoo.com[/TD]
[TD]jackreacher39uk@yahoo.com[/TD]
[TD]jackreacher39uk@yahoo.com[/TD]
[TD]C:\Users\SPACE\Desktop\Cost centre Reports\May 2013[/TD]
[/TR]
[TR]
[TD]USA9262X[/TD]
[TD]IT Central Team[/TD]
[TD]Jim[/TD]
[TD]Smith[/TD]
[TD]jackreacher39uk@yahoo.com[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]C:\Users\SPACE\Desktop\Cost centre Reports\May 2013[/TD]
[/TR]
[TR]
[TD]CH5679[/TD]
[TD]China - Emerging Markets[/TD]
[TD]John[/TD]
[TD]Jones[/TD]
[TD]jackreacher39uk@yahoo.com[/TD]
[TD] [/TD]
[TD]jackreacher39uk@yahoo.com[/TD]
[TD] [/TD]
[TD]jackreacher39uk@yahoo.com[/TD]
[TD]C:\Users\SPACE\Desktop\Cost centre Reports\May 2013[/TD]
[/TR]
[TR]
[TD]SW859512[/TD]
[TD]Sweden Marketing and Comms[/TD]
[TD]Valerie[/TD]
[TD]Fernandez[/TD]
[TD]jackreacher39uk@yahoo.com[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]C:\Users\SPACE\Desktop\Cost centre Reports\May 2013[/TD]
[/TR]
[TR]
[TD]CAN0085[/TD]
[TD]North America HR Division[/TD]
[TD]Clare[/TD]
[TD]Richards[/TD]
[TD]jackreacher39uk@yahoo.com[/TD]
[TD]jackreacher39uk@yahoo.com[/TD]
[TD] [/TD]
[TD]jackreacher39uk@yahoo.com[/TD]
[TD] [/TD]
[TD]C:\Users\SPACE\Desktop\Cost centre Reports\May 2013[/TD]
[/TR]
[TR]
[TD]ES60045[/TD]
[TD]Spain Software Development Team[/TD]
[TD]Tom[/TD]
[TD]Harrold[/TD]
[TD]jackreacher39uk@yahoo.com[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]C:\Users\SPACE\Desktop\Cost centre Reports\May 2013[/TD]
[/TR]
[TR]
[TD]FR000091[/TD]
[TD]France Marketing Division[/TD]
[TD]Michael[/TD]
[TD]Leigh[/TD]
[TD]jackreacher39uk@yahoo.com[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]jackreacher39uk@yahoo.com[/TD]
[TD]C:\Users\SPACE\Desktop\Cost centre Reports\May 2013 [/TD]
[/TR]
</tbody><colgroup><col><col><col span="2"><col><col span="4"><col></colgroup>[/TABLE]

Email (column E) will populate in the To: field
Recipients 2 to 5 (columns F to I) in the CC: field

I would want the email heading to be Fixed Text: "Cost Centre report for - May 2013 (this will change depending on the month and report which is being created/distributed).

I would like the body of the email to say:

Dear First name, (column C)

Please find attached a copy of your cost centre report for May 2013 (again month will be determined by the date of the file)

And a small summary to appear in the email containing the following:

Cost centre: (Column A)
Cost centre Description (Column B)
Cost centre Manager: First name & Surname.

With thanks


Signature (TBC)
 
Upvote 0
Hi Mike,

Using much of the previously posted code, try the following:
I have assumed the date (May 2013) can be taken from the folder name in the Path in column J as the folder name would sepcify the changed month/year?
If any info is wrong check the offset values are looking at the right columns starting from J.

Code: 
Sub Mail_Report()
Dim OutApp As Object
Dim OutMail As Object

'Use presence of a Path to determine if a mail is sent.
    Set rng = Range(Range("J2"), Range("J" & Rows.Count).End(xlUp))
    For Each cell In rng
    Rw = cell.Row
          
    Path = cell.Value
    If Path <> "" Then
     
'Get Date info from Path
     Dte = Right(Path, Len(Path) - InStrRev(Path, "\"))
     
'Get Cost Centre to check for filename (Column A)
     FilNmeStr = cell.Offset(0, -9).Value
'Email Address
     ToName = cell.Offset(0, -5).Value
              
     
'Create Recipient List
    For x = 1 To 4
        Recp = cell.Offset(0, -x).Value
    If Recp <> "" Then
          Recp = cell.Offset(0, -x).Value
    End If
 
        RecpList = RecpList & ";" & Recp
    Next
     
      ccTo = RecpList
      
'Get  Name
     FirstNme = cell.Offset(0, -7).Value
     Surname = cell.Offset(0, -6).Value
         
'Loop through files in Path to see if
     ClientFile = Dir(Path & "\*.*")
 
     Do While ClientFile <> ""
 
     If InStr(ClientFile, FilNmeStr) > 0 Then
 
     AttachFile = Path & "\" & ClientFile
 
     MailBody = "Dear " & FirstNme & vbNewLine & vbNewLine _
     & "Please find attached a copy of your cost centre report for " & Dte _
     & vbNewLine & vbNewLine _
     & "Cost centre: " & cell.Offset(0, -9).Value _
     & vbNewLine & _
     "Cost centre Description: " & cell.Offset(0, -8).Value _
     & vbNewLine & _
     "Cost centre Manager: " & FirstNme & " " & Surname _
     & vbNewLine & _
     "With thanks" & _
      Signature
           
 
   Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(o)
        With OutMail
            .Subject = "Cost Centre Report for - " & Dte
            .To = ToName
            .cc = ccTo
            .Body = MailBody
            .Attachments.Add (AttachFile)
            .Display
            '.Send
    End With
 
        Set OutMail = Nothing
        Set OutApp = Nothing
        RecpList = ""
        
End If
ClientFile = Dir
Loop
End If
Next
End Sub
 
Upvote 0
Glad to help.

The code: Rw = cell.Row is not being used. You can bin it.
 
Upvote 0
Hi Dave, Just a quick question, is there a way to define the mail client within this code? It works perfectly in Outlook, but I need to send the emails via Google mail, as we have recently migrated across?
 
Upvote 0
You must log in or register to reply here.

Similar threads

larinda4
  • Question Question
VBA: Send Outlook Email & Attach PDF
Replies
1
Views
606
Trebor76
T
V
  • Question Question
Excel for Mac 2021 v16.81. Send bulk email with an attachmen
Replies
2
Views
572
vk2023
V
L
  • Locked
  • Question Question
How to stop VBA from creating pdf from drop-down list if the value is empty?
Replies
1
Views
281
Fluff
Fluff
A
  • Solved
Copy Entire Workbook, save as sheet name with current date, hide all sheets except one, & attach to email
Replies
2
Views
470
Amo840812
A
J
  • Question Question
Excel VBA Macro to send emails & attachments
2
Replies
12
Views
735
Fluff
Fluff

Forum statistics

Threads
1,223,240
Messages
6,170,951
Members
452,368
Latest member
jayp2104

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