Mail Merge

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.

Code:
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
 
Upvote 0
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.

Code:
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
 
Upvote 0
Yep, change your code to that below. I've included some error handling code this time - just in case.

Regards,
D

Code:
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
 
Upvote 0
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

Code:
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
Set rngeAddresses = Application.InputBox(prompt:="Please select range containing e-mail addresses.", Title:="Select Range", Type:=8)
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top