mail merge with attachments
mail merge with attachments
(OP)
Hi,
I am trying to run a VB script that generates email with doc attached. I have the script below but inly generates one email - I have 5 rows on my source excel doc. the script below does what i want but only creates one email with the attachment.
Any help here would be great.
I am trying to run a VB script that generates email with doc attached. I have the script below but inly generates one email - I have 5 rows on my source excel doc. the script below does what i want but only creates one email with the attachment.
Any help here would be great.
Sub Single_attachment() Dim appOutlook As Object Dim Email As Object Dim Source, mailto As String Set appOutlook = CreateObject("Outlook.Application") Set Email = appOutlook.CreateItem(olMailItem) mailto = mailto & Cells(2, 2) & ";" Source = "C:\Users\fk\Desktop\test invoices email\" & Cells(2, 3) Email.Attachments.Add Source ThisWorkbook.Save Source = ThisWorkbook.FullName Email.Attachments.Add Source Email.To = mailto Email.Subject = "Important Sheets" Email.Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards." Email.Display End Sub
RE: mail merge with attachments
Dim Source, mailto As String
...
mailto = mailto & Cells(2, 2) & ";"
...
Email.To = mailto
If you have more e-mail addresses in other cells, you need to loop thru them to add them to your mailto variable
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: mail merge with attachments
RE: mail merge with attachments
If you mean '5 rows of e-mail addresses that I want to email to' starting at B2 until B6, then
But...
I hate hard-coded values. Well, starting at row 2 is common because of the header row 1, but how far to loop is another issue.
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: mail merge with attachments
RE: mail merge with attachments
But - only you know what you have and what you are trying to accomplish
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: mail merge with attachments
You should have 2 attachments in your e-mail:
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: mail merge with attachments
CODE -->
the excel doc looks like this:
CODE --> excel
so at the moment I have an email that gets composed when i run the code above but it has the 2 emails in the address field. This is not what i am aiming for - i want two emails to open up with one email address and associated doc attached. I have included a screenshot of the email that comes up when i run what i have.
RE: mail merge with attachments
Are yopu saying you want a separate email for each address (in which case 'Greeting everyone' does not make sense). or are you wanting a separate email for each attachment, but each email to go to all the recipients (in which case 'Please go through the Sheets' doesn't make sense). Or do you want both (?) attachments to go to all the recipients via a single email?
And perhaps you can clarify the spreadsheet a bit. Does each recipient have their own, separate attachment, or are all the listed attachments supposed to go to everyone?
>it has the 2 emails in the address field
Not in the picture you shared. That has, as I would expect from the code, 5 email addresses
And in your tidied up code you have removed bit that adds the current Excel file as an attachment. Was that intentional?
RE: mail merge with attachments
Before looping to next IntR each Eemail should be sent if this has to be done by code
combo
RE: mail merge with attachments
thanks all for help so far
RE: mail merge with attachments
You might want to change .Body to reflect this ...
>how to get this code to send straight away
Change .Display to .Send
RE: mail merge with attachments
RE: mail merge with attachments
?
combo
RE: mail merge with attachments
breaks lines if the body format is text. This can be forced by .Body=BodyFormat=1 after creating mail. HTML format requires <br> tags instead of vbNewLine.
BTW, you can add reference to Microsoft Outlook library, and next use early binding (Dim appOutlook As Outlook.Application, Dim Email As Outlook.MailItem). Advantages: (a) an access to intellisense after each dot, (b) access to named constants (as olFormatPlain=1; you already have olMailItem in your code, it by accident work because olMailItem = 0), (c) possibility to explore Outlook library in the Object browser window.
combo
RE: mail merge with attachments
in terms of the IF.....can I say if the item (pdf in this case) in column C does not exist in the directory specified to continue onto the next row?
RE: mail merge with attachments
Depending on your needs, 'If ... End If' can be a big block starting after 'For' (no file name specified - we don't process the mail) or do not update the 'source' string. In the last case you need additional condition for the case when C2 (and next) are empty.
combo
RE: mail merge with attachments
RE: mail merge with attachments
so as you can see the items in column C are the pdf items for attaching to the email. but sometimes there may not exist this pdf item. so when I run my vb code at the moment it stops when there is no matching pdf item in the folder to attach - I need the code to ignore\don't send that person an email or give a popup message and continue onto the next person on the excel list.
can this be done?
RE: mail merge with attachments
combo
RE: mail merge with attachments
"Please go through the Sheets." - pdf's dont have 'Sheets', do they
combo's guess that "you need five emails to five recipients, with one attachment each" was just a 'shot in the dark' or an 'educated guess' (I would assume
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson