I'm trying to send an email as part of a macro using an email address in a different workbook (that is also open). VLookup is using a Public variable (supplier) to look at the next column for the email address. I've tried lots of different syntaxes, searched this forum for hours, but can't get it to work! I can't figure out how to get it to address the other workbook. What am I doing wrong?
Public Sub SendEmail()
Dim OutApp As Object
Dim OutMail As Object
Dim Dist As String
Dist = worksheetfunction.vlookup(Supplier,[Scar Form.xls]("Supplier Information")!range(a9:z1000), 2)
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = Dist
.CC = ""
.BCC = ""
.Subject = "SCAR #" & (Worksheets("Scar").Cells(8, 29))
.Body = "Please review the attached Supplier Corrective Action Request (SCAR) and respond in a timely manner." & _
vbCrLf & vbCrLf & "" & _
vbCrLf & "Best regards,"
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Public Sub SendEmail()
Dim OutApp As Object
Dim OutMail As Object
Dim Dist As String
Dist = worksheetfunction.vlookup(Supplier,[Scar Form.xls]("Supplier Information")!range(a9:z1000), 2)
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = Dist
.CC = ""
.BCC = ""
.Subject = "SCAR #" & (Worksheets("Scar").Cells(8, 29))
.Body = "Please review the attached Supplier Corrective Action Request (SCAR) and respond in a timely manner." & _
vbCrLf & vbCrLf & "" & _
vbCrLf & "Best regards,"
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub