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

[dbwiz]

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
	A	B	C	D	E
1	Client Name	Email	First Name	Last Name	Path
2	ABC	JaneDoe@abc.com	Jane	Doe	Q:\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!

 

[jackreacher39uk]

Thanks Dave, really appreciate your help and advice. Warm regards.

 

[jackreacher39uk]

Hi Dave, I was wondering if you could help me with a line of coding, I basically need to incorporate the address that the mails are being sent from in Outlook, so perhaps a 'From' syntax? so for example.... displayed text would be: "Monthly Reports" (although this can be changed) and will be seen when someone receives the email, but the address will be reports@......com? Many thanks

 

[daverunt]

Hi Mike,

In Outlook you can use:

.SentOnBehalfOfName = "Emailadress"

 

[jackreacher39uk]

Hi Dave,

I want to add some additional lines of text and spaces, but being a total novice at VBA, I couldn't seem to get the script to work .

I am trying to insert three new sentences, after the 'cost centre manager name', (although not sure of the wording yet), and to insert a line space between each sentence. (shown in caps below)
I would also like to add a line space between the 'with thanks' and 'signature'


"Cost centre Manager: " & FirstNme & " " & Surname _
& vbNewLine & _
FOR DETAILS ON CONTACT...................
NEW LINE
FOR DETAILS ON CONTACT...................
NEW LINE
FOR DETAILS ON CONTACT...................
NEW LINE
"With thanks" & _
NEW LINE
Signature

Thanks ever so much for your help.

 

[daverunt]

Hi Mike,


try this.

MailBody = "Cost centre Manager: " & FirstNme & " " & Surname & vbNewLine _
    & vbNewLine & _
    "FOR DETAILS ON CONTACT..................." & vbNewLine _
    & vbNewLine & _
    "FOR DETAILS ON CONTACT..................." & vbNewLine _
    & vbNewLine & _
    "FOR DETAILS ON CONTACT..................." & vbNewLine _
    & vbNewLine _
    & "With thanks" _
    & vbNewLine & _
    Signature

 

[Vince1984]

Hi. I was wondering if someone can help me with VBA coding for a dashboard tool I am currently developing.
Everytime I input Year to date data on the an input sheet, the pivot tables and charts must update to the same range(A:BC) and refesh automatically.
But the problem is with the slicers that's connected to more than 1 pivot table. I have to disconnect them first in order to allow the pivot/s to accept the new data source. Isnt there a better way I can do this. please if you could assist me?

 

[daverunt]

Hi Vince,

I suggest you put your question in a new post as this has nothing to do with the current one.
You want people to respond who know about pivot tables and charts and they may bypass a post with a subject line about Outlook emails.

PS - I haven't got a clue what your post is about.

 

[daverunt]

Hi Mike,

(FYI - your Inbox is full so no-one can send you messages)

The code needs to get a better match with the filename/Cost centre as you thought.
So we remove the file extension whilst looping and compare the remaining name with the Cost Centre name.


So for example file 12345.pdf becomes 12345 to look for a match with the Cost Centre.
With StrComp the match must be exact. i.e 12345 compared to 1234500 is rejected.

'Loop through files in Path to see if
     ClientFile = Dir(Path & "\*.*")
 
     Do While ClientFile <> ""

'Get Client File Name without extension for comparison to Cost Centre
    CLientFileNoXtN = Left(ClientFile, InStrRev(ClientFile, ".") - 1)
 
     If (StrComp(CLientFileNoXtN, FilNmeStr, vbTextCompare) = 0) Then
 
     AttachFile = Path & "\" & ClientFile

 

[daverunt]

For the other part of your question:
The header changes:
In the original posted mail you send:
Cost centre:
Cost centre Description:
Cost centre Manager:

Are these to be removed/replaced.

What is the new information and which column is the new information in?

 

[jackreacher39uk]

Thanks as always Dave, you are a legend I am sure the code will work I will get back to you regarding the 2nd point.