mail merge with attachments 2

[gussy81]

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.

[pre]
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


[/pre]

 

[Andrzejek]

Yes, your code will create one e-mail with just one e-mail address from cell Cells(2, 2) which is B2:
[tt]
Dim Source, [blue]mailto[/blue] As String
...
[blue]mailto[/blue] = [blue]mailto[/blue] & Cells(2, 2) & ";"
...
Email.To = [blue]mailto[/blue][/tt]

If you have more e-mail addresses in other cells, you need to loop thru them to add them to your [tt]mailto[/tt] variable

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[gussy81]

ah dam i had a feeling - any chance you could help me with this script?

 

[Andrzejek]

I have 5 rows

If you mean '5 rows of e-mail addresses that I want to email to' starting at B2 until B6, then
[pre]
...
Dim intR as Integer
...
intR = 2
For intR = 2 to 6
If intR = 2 then
mailto = Cells(intR, 2)
Else
mailto = mailto & ";" & Cells(intR, 2)
End If
Next intR
Debug.Print "mailto: " & mailto
...
[/pre]

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

 

[gussy81]

oh wow thanks - but what has happened is that one email opens with 2 email addresses in it with the attachment included

 

[Andrzejek]

I am guessing what you have in your Excel file. I'm assuming you have something like:

[pre]
A B C D
1 ABC MAIL TO File ...
2 Mark@abc.com abc.docx
3 Bob@xyz.com
4 Zak@cnn.com
5 Bill@hym.net
6 Iza@Yiu.biz
7[/pre]

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

 

[Andrzejek]

one email opens with 2 email addresses in it with the attachment included

You should have 2 attachments in your e-mail:

[pre]
...
Source = "C:\Users\fk\Desktop\test invoices email\" & Cells(2, 3)
Email.Attachments.Add Source [green]'First attachment, what's in C2[/green]
ThisWorkbook.Save [green]'Why do you save your workbook here?[/green]
Source = ThisWorkbook.FullName
Email.Attachments.Add Source [green]'Second attachment - your Excel file[/green]
...
[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[gussy81]

ok so I have tidied the code up a bit now:

Sub Single_attachment()
Dim appOutlook As Object
Dim Email As Object
Dim Source, mailto As String
Dim intR As Integer

Set appOutlook = CreateObject("Outlook.Application")
Set Email = appOutlook.CreateItem(olMailItem)

intR = 2
For intR = 2 To 6
If intR = 2 Then
 mailto = Cells(intR, 2)
    Else
        mailto = mailto & ";" & Cells(intR, 2)
    End If
Next intR



Source = "C:\Users\fk\Desktop\test eom invoices email\" & Cells(2, 3)
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

the excel doc looks like this:

       A                      B                                C
1 [b]salesman[/b]            email	                      file
2 Mary                      mary@email.com	           0224.pdf
3 John                      john@email.com                 0436.pdf
4 Barbara                   barbara@email.com              1264.pdf
5 Catherine                 catherine@email.com            1047.pdf
6 David                     david@email.com                0875.pdf

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.

 

[strongm]

It isn't actually at all clear what you want.

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?

 

[combo]

If you need five emails to five recipients, with one attachment each, you need to process each email in loop, for instance:

[pre]For intR = 2 To 6
mailto = Cells(intR, 2)
Source = "C:\Users\fk\Desktop\test eom invoices email\" & Cells(IntR, 3)
Set Email = appOutlook.CreateItem(olMailItem)
With Email
.To = mailto
.Attachments.Add Source
.Subject = "Important Sheets"
.Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards."
.Display
End With
Next intR
[/pre]

Before looping to next IntR each Eemail should be sent if this has to be done by code

combo

 

[gussy81]

Combo that's exactly what I was after!!! thank you. now I just have to figure out how to get this code to send straight away or [ut into outbox....ill investigate this now i have made some progress.

thanks all for help so far

 

[strongm]

>Combo that's exactly what I was after!

You might want to change .Body to reflect this ...

>how to get this code to send straight away

Change .Display to .Send

 

[gussy81]

perfect!!! i am almost there - just testing it now. would i need an if statement somewhere in the loop - as if the attachment does not exist the code falls over. so if the 2nd person on the list has a pdf to go to them and there is no pdf can that persons email be skipped?

 

[combo]

[tt]If Cells(IntR, 3) <> "" Then[/tt]
?

combo

 

[combo]

[tt].Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards."[/tt]
breaks lines if the body format is text. This can be forced by [tt].Body=BodyFormat=1[/tt] 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 ([tt]Dim appOutlook As Outlook.Application[/tt], [tt]Dim Email As Outlook.MailItem[/tt]). 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

 

[gussy81]

ok Combo thanks for the advice - this is all a learning curve for me.

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?

 

[combo]

[tt]If Cells(IntR, 3) <> ""[/tt] means that there is no text in the cell. The presence of file and directory is another story.
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

 

[gussy81]

yeah its really for a situation where there is no pdf for a person on the excel list in the folder - but there may be the next time the mailshot takes place.

 

[gussy81]

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?

 

[combo]

You can use Dir function to test if file exists. If no match, zero-length string is returned.

combo