Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Mail Merge

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Cleethorpes/UK
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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


  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Guest

    Default

    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

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Guest

    Default

    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

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    Guest

    Default

    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?


  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  9. #9
    Guest

    Default

    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?

  10. #10
    Guest

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •