Outlook send email from data in column from excel spreadsheet

[benasalisbury]

Hi all

I want to create a macro (I presume the best practice would be in outlook) which will create a new email and set the subject/message as shown in the spreadsheet (attached) and send it to the list of email addresses in column A automatically.

Any ideas? Is outlook the best place to create the macro, or can it be done in excel.

Thanks,
Ben

Example 1.xlsx

[JieJenn]

Try

Please Login or Register  to view this content.

[benasalisbury]

Thank you! The macro works brilliantly, however only sends an email for the first email (A1) and doesn't email each address in each cell

[JieJenn]

Yeah I realized that too. I am not too familiar with outlook, but try this revision

Please Login or Register  to view this content.

[benasalisbury]

Brilliant! Works perfectly! Thank you.

I'd also like to know what each part of the macro means, just for my own personal reference and what it is doing, would you be able to let me know, either here or via email?

[JieJenn]

Basically you're opening outlook using the macro Set OutApp = CreateObject("Outlook.Application") then creating a mail Set OutMail = OutApp.createitem(0)

then sending the mail
.To = cell
.Subject = Range("C1")
.Body = Range("C2")
.display
'.send (Optional)

Then close outlook
Set OutMail = Nothing
Set OutApp = Nothing

[bensalisbury]

Thank you for all your help!

I have modified the macro slightly to include a range of A2:A100

Could you tell me what I need to do for the following

IF a cell is blank, move onto the next one (not sending an email

[pulguita0810]

Hi,

This thread is 99.99% of what i am after. Is there anyway to keep the Outlook sent item open prior to sending? What do I need to add to the code to make this happen rather than sending automatically?

The reason i'd like it to stay open is to format the text as it seems to strip the formatting from the text.

Any help would be great!

Thank you!

[OnErrorGoto0]

Did you try the code? It doesn't actually send the email as written, just display it.

[pulguita0810]

Yes I tested it and it does send automatically and the email is the body of the text in the word document - but it's displayed as text and stripped the formatting.

[ran1]

Thanks.

Works great. 1. How would you add an attachment? and attachments changes as per email id's.