VBA: send emails to excel adresses

[mgergolet]

Hi guys!

I'd need a big help from you!

I have an excel file with about 200 email addresses in it. They are listed in column D. I need to compose a text message with an attachment and to send this mail to these addresses in bcc.

Is there a way to create a way, a macro or some kind of program that would allow me to put all this addresses in an empty outlook 2003 message?

Office version is 2003. I really hope you can help me!

Ciao

Simon

 

[daverunt]

Hi,

This will add all names in column D to the BCC list.
The attach file path needs to be set as does the mail body, subject and
main recipient.
At the moment send is commented out so it displays the message for you to view/correct.
You can use it that way or comment out display and uncomment send.

Sub BCCMany()
 
Dim OutApp As Object
Dim OutMail As Object
Dim EmailSubject As String
Dim EmailSendTo As String
Dim MailBody As String
Dim cell As Range
 
 AttFile = "C:\junk\12345.pdf"
 
 EmailSendTo = "[EMAIL="Test@test.com"]Test@test.com[/EMAIL]"
 
'Subject string
    EmailSubject = "test"
 
'Mail Body
    MailBody = "Test Mail"
 
'Contacts list creates BCC list from Column D    
    Set rng = Range(Range("D1"), Range("D" & Rows.Count).End(xlUp))
 
    For Each cell In rng.Rows
 
    If cell.Value <> "" Then
      BCCList = BCCList & cell.Value & ";"
    End If
   Next
 
'Send Mail
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(o)
        With OutMail
            .Subject = EmailSubject
            .To = EmailSendTo
            .BCC = BCCList
            .body = MailBody
            .Attachments.Add AttFile
            .Display
            '.send
        End With
 
        Set OutMail = Nothing
        Set OutApp = Nothing
 
End Sub

 

[mgergolet]

Great! I'll try it tomorrow and let you know if it is what I need! I am new to VBA and this stuff, so I'll be a bit in trouble, but i'll let you know!

For now, many thanks!

 

[mgergolet]

With small changes I managed to do what I was looking for! Thank you sooooo much!

 

[mgergolet]

Small addition!

I need to select the range that is not only one column, but two columns.

How do I need to alter this to obtain the result?

'Contacts list creates BCC list from Column D
Set rng = Range(Range("n2"), Range("n" & Rows.Count).End(xlUp))

For Each cell In rng.Rows

If cell.Value <> "" Then
BCCList = BCCList & cell.Value & ";"
End If
Next

The column I need to add contains sometimes a secondary email (but most of the times is blank). I need all those addresses to be added to the BCCList too...

Thanks in advance!

 

[wigi]

Set rng = Range(Range("n2"), Range("n" & Rows.Count).End(xlUp)).Resize(,2)

 

[mgergolet]

Set rng = Range(Range("n2"), Range("n" & Rows.Count).End(xlUp)).Resize(,2)

No, I get an error:
run-type error '13':
Type mismatch

and it highlights me

If cell.Value <> "" Then



If it can help, it might be that the whole column is empty, or there is only one voice in the entire column while the N one has some 1500...

 

[wigi]

For Each cell In rng

instead of:

For Each cell In rng.Rows

 

[mgergolet]

For Each cell In rng

instead of:

For Each cell In rng.Rows

Super!

Thanks for the quick and perfect help!

Have a nice day!