Sending an Email by referencing another sheet

[ChaosPup]

Hi all,

I have the code below working to send an email from a workbook (ignore the garbage address obviously) -

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!

 

[Engberg]

This worked perfectly. The only other question I have is - one of the addresses it goes to will be fixed, the other will be defined by the code you gave me. Is it possible to send them both on the .To command? I tried it in various formats but couldn't get it to work. I was hoping to copy the email to more than 1 fixed address as well.

You change your .To-line like this.

1 fix address:

.To = "abc@email.com;" & emailValue

Multiple fixed addresses:

.To = "abc@email.com;def@email.com" & emailValue

 

[Engberg]

Mistake on the multiple fixed, you need a semicolon after each address, sorry:

.To = "abc@email.com;def@email.com;" & emailValue