Mail Merge

[angelrichardson]

how do i do a mail merge- to e-mail addresses within excel? i would appreciate help! Cheers!

 

[dk]

Angel,

give this a go. You need to set a reference to the Microsoft Outlook Object Library (Tools, References in the VB editor) in order for this code to work.

Sub EmailMerge()
Dim olApp As Outlook.Application, olMail As Outlook.MailItem
Dim rngeAddresses As Range, rngeCell As Range

Set olApp = New Outlook.Application

'Set this to where your addresses are
Set rngeAddresses = Sheets("Sheet1").Range("A1:A5")

For Each rngeCell In rngeAddresses.Cells
    Set olMail = olApp.CreateItem(olMailItem)
    olMail.To = rngeCell.Value
    olMail.Subject = "It's Friday"
    olMail.Body = "The weekend is almost upon us - drink beer and be merry!"
    olMail.Send
Next

End Sub

HTH,
D

 

[Guest]

On 2002-03-01 03:55, dk wrote:
Angel,

give this a go. You need to set a reference to the Microsoft Outlook Object Library (Tools, References in the VB editor) in order for this code to work.

Sub EmailMerge()
Dim olApp As Outlook.Application, olMail As Outlook.MailItem
Dim rngeAddresses As Range, rngeCell As Range

Set olApp = New Outlook.Application

'Set this to where your addresses are
Set rngeAddresses = Sheets("Sheet1").Range("A1:A5")

For Each rngeCell In rngeAddresses.Cells
    Set olMail = olApp.CreateItem(olMailItem)
    olMail.To = rngeCell.Value
    olMail.Subject = "It's Friday"
    olMail.Body = "The weekend is almost upon us - drink beer and be merry!"
    olMail.Send
Next

End Sub

HTH,
D

Can I change the Range ("A1:A5") trough an input box : Put in from ... to ...
(from to are the linenumbers)
Many thanks

 

[dk]

Yep, change your code to that below. I've included some error handling code this time - just in case.

Regards,
D

Sub EmailMerge()
Dim olApp As Outlook.Application, olMail As Outlook.MailItem
Dim rngeAddresses As Range, rngeCell As Range

On Error GoTo ErrHandler

Set olApp = New Outlook.Application

Set rngeAddresses = Application.InputBox("Please select range containing e-mail addresses.", "Select Range", , , , , , :cool:

On Error Resume Next
If rngeAddresses Is Nothing Then Exit Sub    'User cancelled
On Error GoTo ErrHandler

For Each rngeCell In rngeAddresses.Cells
    Set olMail = olApp.CreateItem(olMailItem)
    olMail.To = rngeCell.Value
    olMail.Subject = "It's Friday"
    olMail.Body = "The weekend is almost upon us - drink beer and be merry!"
    olMail.Send
Next

Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation, "An error has occurred"
End Sub

 

[Guest]

On 2002-03-01 08:17, dk wrote:
Yep, change your code to that below. I've included some error handling code this time - just in case.

Regards,
D

Sub EmailMerge()
Dim olApp As Outlook.Application, olMail As Outlook.MailItem
Dim rngeAddresses As Range, rngeCell As Range

On Error GoTo ErrHandler

Set olApp = New Outlook.Application

Set rngeAddresses = Application.InputBox("Please select range containing e-mail addresses.", "Select Range", , , , , , [img]/board/images/smiles/icon_cool.gif[/img]

On Error Resume Next
If rngeAddresses Is Nothing Then Exit Sub    'User cancelled
On Error GoTo ErrHandler

For Each rngeCell In rngeAddresses.Cells
    Set olMail = olApp.CreateItem(olMailItem)
    olMail.To = rngeCell.Value
    olMail.Subject = "It's Friday"
    olMail.Body = "The weekend is almost upon us - drink beer and be merry!"
    olMail.Send
Next

Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation, "An error has occurred"
End Sub

There must be an error in your macro on the line "Set rnge Adresses = Application ......
Can you give a solution please?
Many thanks

 

[dk]

The smiley shouldn't be there on that line. Replace all the crap (IMG SOURCE=blah blah) with the number 8 (that's eight just in case in gets screwed up again).

D

 

[Guest]

On 2002-03-01 08:46, dk wrote:
The smiley shouldn't be there on that line. Replace all the crap (IMG SOURCE=blah blah) with the number 8 (that's eight just in case in gets screwed up again).

D

This is the line now:Set rngeAddresses = Application.InputBox("Please select range containing e-mail addresses."),, "Select Range"
but is does not work.
What I do wrong?

 

[dk]

Set rngeAddresses = Application.InputBox(prompt:="Please select range containing e-mail addresses.", Title:="Select Range", Type:=8)

 

[Guest]

On 2002-03-01 09:16, Anonymous wrote:

On 2002-03-01 08:46, dk wrote:
The smiley shouldn't be there on that line. Replace all the crap (IMG SOURCE=blah blah) with the number 8 (that's eight just in case in gets screwed up again).

D

This is the line now:Set rngeAddresses = Application.InputBox("Please select range containing e-mail addresses."),, "Select Range"
but is does not work.
What I do wrong?

Excuse me,I have found the error.
But the inputbox does not give me:
from ....... to .......
(I mean that I want to input the linenumbers because all my e-mail adresses are in colom A)So I like to input : 160 165 and then enter to send all to the e-mails who are in line 160 to 165
Can you change the macro?

 

[Guest]

On 2002-03-01 09:34, Anonymous wrote:

On 2002-03-01 09:16, Anonymous wrote:

On 2002-03-01 08:46, dk wrote:
The smiley shouldn't be there on that line. Replace all the crap (IMG SOURCE=blah blah) with the number 8 (that's eight just in case in gets screwed up again).

D

This is the line now:Set rngeAddresses = Application.InputBox("Please select range containing e-mail addresses."),, "Select Range"
but is does not work.
What I do wrong?

Excuse me,I have found the error.

Still a little question about merge mail.
All my e-mail adresses are in sheet A
Text for body the e-mail is in sheet B
How can I merge this body to each mail adresses?
Have you a solution about that?
Many thanks