macro to launch outlook

  carmella 12:13 17 Apr 03
Locked

I'm not sure if this is possible: I have an Excel document with contact names and email addresses. I'd like to be able to select a number of addresses and send email (oultook) to all addresses at once. (one email to all, not one per address)
Any ideas?
many thanks
C

  MartinT-B 15:38 17 Apr 03

Don't have Outlook, have OE.

In OE go to your address book, Click New and choose NEW GROUP.

This opens another box. Give the Group a name.

Then add names to the group by clicking select members. You can choose any or all of the names already in your address book, or you can add new ones.

Once the Group is created it appears with a different icon in the Address list to the left.

Click in it to create an email to all the group, or click create mail and type the group name into the To: box.

I assume this will work with Outlook.

If not........

  VoG™ 16:42 17 Apr 03

You can do this using an Excel macro. Start Excel and press ALT+F11 to enter the Visual Basic Editor.

Insert/Module

Copy the following code and paste it into the module:

Sub Email_It()

Dim objOutlk As Outlook.Application

Dim objOutlkMsg As Outlook.MailItem

Dim objOutlkRecip As Outlook.Recipient

Dim Subject, Msgbody As String

Dim cel As Range, sto As String

Subject = "Your subject line goes here"

Msgbody = "Dear All" _

& vbCrLf & vbCrLf & "Your message goes here" _

& vbCrLf & vbCrLf & vbCrLf & "Regards," _

& vbCrLf & "Carmella"

Set objOutlk = CreateObject("Outlook.Application")

Set objOutlkMsg = objOutlk.CreateItem(olMailItem)

With objOutlkMsg

For Each cel In Selection

sto = cel.Value

Set objOutlkRecip = .Recipients.Add(sto)

objOutlkRecip.Type = olTo

Next cel

.Subject = Subject

.Body = Msgbody

.Save

.Send

End With

Set objOutlk = Nothing

End Sub

(The above line is the end of the code.)

Still in your module, Tools/References, scroll down the list and put a tick in "Microsoft Outlook 10.0 Object Library" (your version may not be 10.0).

ALT+F11 to exit the VBE.

On your sheet with the e-mail addresses, select those that you want to sent to and Tools/Macro/Macros, select Email_It and click Run. You will have to click yes to security warnings that might come up about another application trying to send an e-mail.

If your subject line and message body are also stored on the Excel sheet, these could be incorporated automatically.

  carmella 16:59 17 Apr 03

thanks for this. I'm getting the following syntax error, though:

& vbCrLf & vbCrLf & "Your message goes here" _

any help appreciated.
C

  carmella 16:59 17 Apr 03

thanks for this. I'm getting the following syntax error, though:

& vbCrLf & vbCrLf & "Your message goes here" _

any help appreciated.
C

  carmella 16:59 17 Apr 03

thanks for this. I'm getting the following syntax error, though:

& vbCrLf & vbCrLf & "Your message goes here" _

any help appreciated.
C

  VoG™ 17:03 17 Apr 03

Msgbody = "Dear All" & vbCrLf & vbCrLf & "Your message goes here" & vbCrLf & vbCrLf & vbCrLf & "Regards," & vbCrLf & "Carmella"

  carmella 17:08 17 Apr 03

many thanks for this, it works wonderfully.
hope you have a nice easter.
C

This thread is now locked and can not be replied to.

Amazon Fire HD 8 review: A brilliant combination of function and value – with one massive caveat

1995-2015: How technology has changed the world in 20 years

How to create an introvert-friendly workplace

Apple Watch Series 2 review | Apple Watch 2 review: New Apple Watch is faster, brighter,…