I created a VBA which will send a sheet of a excelfile to a fixed email address in the VBA code. That is nice, but what I really want is the VBA to look at a particular cell in the activesheet (M2) to pick up the email address from there. Below is a part of the VBA code. In the section .To = "ghn@xyz.nl" I want a code which will pick up the email address. Does anyone know which code I can use??
TempFilePath = Environ$("temp") & "\"
TempFileName = "Fuel " & " " & Format(Now, "dd-mmm-yy")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = "ghn@xyz.nl"
.CC = ""
.BCC = ""
.Subject = "Fuel"
.Body = "Dear Sir,"
.Attachments.Add Destwb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0
.Close savechanges:=False
End With
Bookmarks