Excel 2007 Email Macro

[trussman]

I tried this in the Office thread and was told to start it here.

I am looking for code to take the emails listed in a column in Excel that would automatically take all those emails and put them into one email.

Example:

Email Address Name Company
myemail@mydomain.com Joe Schmoe ABC Architects
youremail@mydomain.com Jane Doe XYZ Designers
heremail@theirdomain.com Mary Jane Jane's Designs

Email address 'To' line: myemail@mydomain.com; youremail@mydomain.com; heremail@theirdomain.com

I have a list of 60+ emails in this Excel file. I would like to hit a button that would open a new email and insert all these addresses in the 'To' box.

I am using Office 2007 for everything.

Thanks for any help.

 

[VRoscioli]

First, you'll want to add Outlook to your references in the VBE. Then, you can create an Outlook Application object and create a new mail message with:

Dim ol as Outlook.Application
Set ol = CreateObject("Outlook.Application")

Dim newMessage as Outlook.MailItem
Set newMessage = ol.CreateItem(olMailItem)

Then you can loop through the excel file and add recipients (this assumes they are in column A):

Dim rec as Outlook.Recipient
Dim y as Integer
y=2
Do While Range("a" & y).Value <> ""
    Set rec = newMessage.Recipients.Add(Range("a" & y).Value)
    y=y+1
Loop

Finally, display the message:

newMessage.Display

This might need some tinkering, as I just kinda made it up off the top of my head, but it should be a good jumping off point for you.


-V

 

[trussman]

Thanks for the help. I may be back. I am not much in writing scripts and programs. So this will be an experience for me.