×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

mail merge with attachments
2

thread707-1821725

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.

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

Yes, your code will create one e-mail with just one e-mail address from cell Cells(2, 2) which is B2:

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

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

RE: mail merge with attachments

Quote (gussy81)

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
...
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
...

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

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

RE: mail merge with attachments

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

    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

But - only you know what you have and what you are trying to accomplish sad

---- Andy

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

RE: mail merge with attachments

Quote (gussy81)

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

You should have 2 attachments in your e-mail:

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

---- Andy

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

RE: mail merge with attachments

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

CODE --> 

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:

CODE --> excel

A                      B                                C
1 salesman            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.

RE: mail merge with attachments

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?

RE: mail merge with attachments

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

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

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

combo

RE: mail merge with attachments

(OP)
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

RE: mail merge with attachments

>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

RE: mail merge with attachments

(OP)
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?

RE: mail merge with attachments

If Cells(IntR, 3) <> "" Then
?

combo

RE: mail merge with attachments

.Body = "Greetings Everyone," & vbNewLine & "Please go through the Sheets." & vbNewLine & "Regards."
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

(OP)
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?

RE: mail merge with attachments

If Cells(IntR, 3) <> "" 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

RE: mail merge with attachments

(OP)
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.

RE: mail merge with attachments

(OP)


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

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

combo

RE: mail merge with attachments

"Greetings Everyone" - sounds weird when you send an e-mail to just one person and you have this person's name in column A. Use it.
"Please go through the Sheets." - pdf's dont have 'Sheets', do they ponder

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 smile ) because you had never specified this in your posts. If you would state clearly your requirements, you would be done a long time ago.

---- Andy

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close