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

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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
Cost Centre
Cost Centre Description
First Name
Surname
Email
Recipient 2
Recipient 3
Recipient 4
Recipient 5
Path
IN75867
Off Shore Development team
Joe
Bloggs
jackreacher39uk@yahoo.com
jackreacher39uk@yahoo.com
jackreacher39uk@yahoo.com
jackreacher39uk@yahoo.com
jackreacher39uk@yahoo.com
C:\Users\SPACE\Desktop\Cost centre Reports\May 2013
USA9262X
IT Central Team
Jim
Smith
jackreacher39uk@yahoo.com
C:\Users\SPACE\Desktop\Cost centre Reports\May 2013
CH5679
China - Emerging Markets
John
Jones
jackreacher39uk@yahoo.com
jackreacher39uk@yahoo.com
jackreacher39uk@yahoo.com
C:\Users\SPACE\Desktop\Cost centre Reports\May 2013
SW859512
Sweden Marketing and Comms
Valerie
Fernandez
jackreacher39uk@yahoo.com
C:\Users\SPACE\Desktop\Cost centre Reports\May 2013
CAN0085
North America HR Division
Clare
Richards
jackreacher39uk@yahoo.com
jackreacher39uk@yahoo.com
jackreacher39uk@yahoo.com
C:\Users\SPACE\Desktop\Cost centre Reports\May 2013
ES60045
Spain Software Development Team
Tom
Harrold
jackreacher39uk@yahoo.com
C:\Users\SPACE\Desktop\Cost centre Reports\May 2013
FR000091
France Marketing Division
Michael
Leigh
jackreacher39uk@yahoo.com
jackreacher39uk@yahoo.com
C:\Users\SPACE\Desktop\Cost centre Reports\May 2013

<tbody>
</tbody>

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:

Cost CentreCost Centre DescriptionFirst NameSurnameEmailRecipient 2Recipient 3Recipient 4Recipient 5Path
IN75867Off Shore Development teamJoeBloggsjackreacher39uk@yahoo.comjackreacher39uk@yahoo.comjackreacher39uk@yahoo.comjackreacher39uk@yahoo.comjackreacher39uk@yahoo.comC:\Users\SPACE\Desktop\Cost centre Reports\May 2013
USA9262XIT Central TeamJimSmithjackreacher39uk@yahoo.com C:\Users\SPACE\Desktop\Cost centre Reports\May 2013
CH5679China - Emerging MarketsJohnJonesjackreacher39uk@yahoo.com jackreacher39uk@yahoo.com jackreacher39uk@yahoo.comC:\Users\SPACE\Desktop\Cost centre Reports\May 2013
SW859512Sweden Marketing and CommsValerieFernandezjackreacher39uk@yahoo.com C:\Users\SPACE\Desktop\Cost centre Reports\May 2013
CAN0085North America HR DivisionClareRichardsjackreacher39uk@yahoo.comjackreacher39uk@yahoo.com jackreacher39uk@yahoo.com C:\Users\SPACE\Desktop\Cost centre Reports\May 2013
ES60045Spain Software Development TeamTomHarroldjackreacher39uk@yahoo.com C:\Users\SPACE\Desktop\Cost centre Reports\May 2013
FR000091France Marketing DivisionMichaelLeighjackreacher39uk@yahoo.com jackreacher39uk@yahoo.comC:\Users\SPACE\Desktop\Cost centre Reports\May 2013

<tbody>
</tbody><colgroup><col><col><col span="2"><col><col span="4"><col></colgroup>

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

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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