VBA Code for multiple emails using Outlook

[WILTSBOY]

Hi,
First posting on this forum so please be patient with me lol. I will include as much info as possible.

My aim is to create an excel spreadsheet that holds numerous (2,500 approx) email addresses in column A of a worksheet called "Email Addresses" in a Workbook called "Distribution.xls"
I would then like to email a pdf file as a single attachment to each recipient (do not want anybody else seeing the other 2,499 email addresses though).
The attachment can be either a separate pdf file located within a network location at my place of work or be embedded with another workbook or be a word document again located within a network file location - whichever is easier. Basically just want each recipient to be able to open and read it.
The Email title and text will be generic.
EG
Title "January 2016 File"
Body of email
"Dear Sir/madam,
Please find attached a communication from us for you to read and action.
Regards,
XXXXXX "
I would also like to be aware of any emails that failed due to the email address not being valid etc. Perhaps filling the cell in column A with a different colour ?
Can you also just confirmm where/how I insert the <ACRONYM title="visual basic for applications">VBA</ACRONYM> code within the "Distribution.xls" spreadsheet.
Anything else that you can suggest that would help the process please include.
Hope I've covered everything.
Many thanks in advance.
PK​

 

[mole999]

is your pdf generic, otherwise just blind carbon copy (BCC) all at once.

I think what you need to do is take this as many separate pieces, and get each to work individually, then pull them together

 

[WILTSBOY]

Hi,
Yes the pdf is generic - same attachment for all users.
How do I get the vba code to put the email addresses from column A in Excel into bcc of the email (am assuming I could "send" the email to myself in the "To" area?
Would be happy to be led by you in a step by step instruction.

 

[mole999]

not 100% sure,
but some one here could build an array that takes your email address one by one from the rows,
wraps them in " ", puts "; " in between each, then I think you assign it to
.BCC rather than .To

obviously just test on two emails belonging to you until you have it right

 

[WILTSBOY]

Thanks.
Am hoping someone out there will be able to help.

 

[tonyyy]

Following up on @mole999's suggestion to use the bcc field, the code below might be a start...

Sub SendMultipleEmails()

Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim LastRow As Long
Dim DList As String

On Error Resume Next
    Set OutApp = GetObject(, "Outlook.Application")
    If OutApp Is Nothing Then Set OutApp = CreateObject("Outlook.Application")
On Error GoTo 0

LastRow = Workbooks("Distribution.xls").Sheets("Email Addresses").Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In Workbooks("Distribution.xls").Sheets("Email Addresses").Range("A1:A" & LastRow)
    If DList = "" Then
        DList = cell.Value
    Else
        DList = DList & "; " & cell.Value
    End If
Next cell

Set OutMail = OutApp.CreateItem(0)
With OutMail
    .To = "wiltsboy@somewhere.com" 'Change to your email address
    .bcc = DList
    .Subject = "January 2016 File"
    .Body = "Dear Sir/madam," _
            & vbNewLine & vbNewLine _
            & "Please find attached a communication from us for you to read and action." _
            & vbNewLine & vbNewLine _
            & "Regards," _
            & vbNewLine & vbNewLine _
            & "XXXXXX"
    .Attachments.Add ("C:\Doc's 2015\2015 Gigs\xl1.pdf") 'Change to your path and filename
    .Send
End With

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

A few comments...

You'll need to make two adjustments to the code. 1) Enter your correct email address where it reads"wiltsboy@somewhere.com"; 2) Following the .Attachments.Add method, change the path and file name to match your pdf.

Review the Contextures tutorial to learn where and how to insert VBA code. (I put the code in it's own .xlsm workbook, then referenced the Distribution.xls workbook for the email addresses.)

There may be a limit as to how many recipients can be entered into a bcc field. You might want to google that before trying to send.

Sending ~2500 emails might be considered spam by your company and/or ISP. You should understand the policies and potential consequences if you decide to proceed.

The code does not address the capture of invalid email addresses. You might want to create an Outlook rule to divert failed emails to a special folder, or just drag and drop them. Another macro could possibly be written in Outlook to capture the failed addresses, then transfer them to Excel where you could then annotate your original list. And yet another thought, the failed emails could be saved to a folder on your hard drive, then an Excel macro could open each message and parse the email address.

Happy New Year!

tonyyy

 

[WILTSBOY]

Many thanks will read the Contextures tutorial and try the code with your suggested alterations. Will Outlook use the selected email profile that I have set at that point in time? Reason I ask is that my organisation will want the distribution to be sent from a generic email address box rather than my personal one. For security reasons in case of replies. We can always add a "do not reply" comment to the body of the email.
Thanks again. Happy New Year.

 

[mole999]

quick look through the code provided looks like excel will use the open account, so having outlook open, you may want to eliminate the .To, and you might be able to set a .From being your company account, providing you have system authority to send on its behalf

Thats why testing is important on just a couple of addresses you can control and monitor

you would also be able to set the path and filename from a line on the spreedsheet somewhere