Hi all,
I have the code below working to send an email from a workbook (ignore the garbage address obviously) -
What I want to do instead of manually inserting an email address after .To is for the macro to look at the highlighted cell and reference it to the email address which will be listed in a separate sheet. For example, if sheet 1 has the initials AM in the highlighted cell, I want it to find the address on sheet 2 which matches the initials AM. Anyone have any ideas? Thanks!
I have the code below working to send an email from a workbook (ignore the garbage address obviously) -
VBA Code:
Sub Mail_small_Text_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "Hello" & vbNewLine & vbNewLine & _
"text1." & vbNewLine & vbNewLine & _
"text2"
On Error Resume Next
With OutMail
.To = "emailaddress@emailaddress.com"
.CC = ""
.BCC = ""
.Subject = "Subject Line"
.Body = strbody
'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
What I want to do instead of manually inserting an email address after .To is for the macro to look at the highlighted cell and reference it to the email address which will be listed in a separate sheet. For example, if sheet 1 has the initials AM in the highlighted cell, I want it to find the address on sheet 2 which matches the initials AM. Anyone have any ideas? Thanks!