Outlook send email from data in column from excel spreadsheet
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
Try
Sub mailOutlook() Dim OutApp As Object: Set OutApp = CreateObject("Outlook.Application") Dim OutMail As Object: Set OutMail = OutApp.createitem(0) Dim stFname As String Dim cell As Range On Error Resume Next For Each cell In Range("A1:A5") With OutMail .To = cell .Subject = Range("C1") .Body = Range("C2") .display .send End With Next cell Set OutMail = Nothing Set OutApp = Nothing End Sub
Last edited by JieJenn; 01-31-2012 at 11:51 AM. Reason: Forgot to finish the code
To thank someone who has helped you, click on the star icon below their name.
I hate reading
Portfolio
I need a job.I am young and incompetent
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
Yeah I realized that too. I am not too familiar with outlook, but try this revision
Sub mailOutlook() Dim OutApp As Object Dim OutMail As Object Dim stFname As String Dim cell As Range For Each cell In Range("A1:A5") Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.createitem(0) With OutMail .To = cell .Subject = Range("C1") .Body = Range("C2") .display '.send (Optional) End With Next cell Set OutMail = Nothing Set OutApp = Nothing End Sub
To thank someone who has helped you, click on the star icon below their name.
I hate reading
Portfolio
I need a job.I am young and incompetent
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?
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
To thank someone who has helped you, click on the star icon below their name.
I hate reading
Portfolio
I need a job.I am young and incompetent
Re: Outlook send email from data in column from excel spreadsheet
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
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!
Did you try the code? It doesn't actually send the email as written, just display it.
Good luck.
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.
Thanks.
Works great. 1. How would you add an attachment? and attachments changes as per email id's.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks