Results 1 to 6 of 6

Thread: VBA Code to email using Globals Address Book

  1. #1
    Junior Member
    Join Date
    May 2013
    Posts
    21
    Rep Power
    0

    VBA Code to email using Globals Address Book

    I need to have a VBA code to look at the name in column AR and then find the email in the global address book then send the email please. Thanks

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    May 2013
    Posts
    21
    Rep Power
    0
    I am new to VBA and do not really understand what this is saying. Can someone help? Here is what I have so far but it is pulling over all information in my spreadsheet and not looking anything up in the global address book. Thanks

    Code:
    Private Sub CommandButton1_Click()
        
         Dim OutApp As Object
        Dim OutMail As Object
        Dim Recipients As String, c As Range
        For Each c In Range("AR2").CurrentRegion
            Recipients = Recipients & ";" & c.Value
        Next c
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        On Error Resume Next
             With OutMail
            .To = "Chase MidWest CPC Weekend Work" & Recipients
            .Cc = "cdurfey@tyco.com"
            .Bcc = ""
            .Subject = "Chase CPC Weekend Work"
            .Body = "Here is the Chase CPC weekend work. Thanks"
            .Attachments.Add ActiveWorkbook.FullName
            .Display
            End With
        On Error GoTo 0
        Set OutMail = Nothing
        Set OutApp = Nothing
    
    End Sub
    Last edited by Excel Fox; 05-28-2013 at 10:26 PM. Reason: Code Tags Added

  4. #4
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    12
    http://www.excelfox.com/forum/f2/vbs...nd-email-1031/
    What's wrong with the above where you didnd't bother to give any feedback.

  5. #5
    Junior Member
    Join Date
    May 2013
    Posts
    21
    Rep Power
    0
    Can you please let me know what is wrong with this code? It keeps pulling over all information in my spreadsheet and not looking anything up in the global address book. Thanks

    Code:
    Private Sub CommandButton1_Click()
    
    Dim OutApp As Object
    Dim OutMail As Object
    Dim Recipients As String, c As Range
    For Each c In Range("AR2").CurrentRegion
    Recipients = Recipients & ";" & c.Value
    Next c
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
    .To = "Chase MidWest CPC Weekend Work" & Recipients
    .Cc = "cdurfey@tyco.com"
    .Bcc = ""
    .Subject = "Chase CPC Weekend Work"
    .Body = "Here is the Chase CPC weekend work. Thanks"
    .Attachments.Add ActiveWorkbook.FullName
    .Display
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
    
    End Sub
    Last edited by Excel Fox; 05-28-2013 at 10:23 PM. Reason: Code Tags Added

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    cdurfey, please add code tags to your code while posting.

    The code snippet you've mentioned above doesn't have a single line that makes it look at the GAL. Why would you think it would? Also, the URL I posted explains how you can do what you have asked. Why don't you look it up?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. VBA To Extract Email Address From Text
    By dunndealpr in forum Excel Help
    Replies: 43
    Last Post: 06-05-2019, 03:56 PM
  2. VBA Code To Email Multiple Recipients From Excel
    By cdurfey in forum Excel Help
    Replies: 4
    Last Post: 06-11-2013, 12:18 AM
  3. Replies: 2
    Last Post: 05-23-2013, 08:08 AM
  4. Replies: 1
    Last Post: 08-21-2012, 07:36 PM
  5. Replies: 6
    Last Post: 04-03-2011, 09:46 PM

Tags for this Thread

Posting Permissions

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