VBA Code to email using Globals Address Book

[cdurfey]

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

[Excel Fox]

You might find Extract data from the Global Address book with VBA (http://social.msdn.microsoft.com/Forums/en-US/outlookdev/thread/183a9b77-7f5a-4afc-91e6-fbe2914c5f78) useful

[cdurfey]

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


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

[bakerman]

http://www.excelfox.com/forum/f2/vbs-code-to-pull-email-smtp-address-from-outlook-and-send-email-1031/
What's wrong with the above where you didnd't bother to give any feedback.:(

[cdurfey]

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


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

[Excel Fox]

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?