Giday to you all
I was trying to manupilate the codes that I found on the internet where I can attach a sheet to email in Outlook but I want to display the email before sending so that I can change the email address from time to time instead of me have to go back to VBA windows and keep changing email address in the codes.
I'm about 90% successful but the email send 2 same attachments instead of 1.
Sub EmailActiveSheetWithOutlook2()
Dim oApp, oMail As Object, _
tWB, cWB As Workbook, _
FileName, FilePath As String
Application.ScreenUpdating = False
'Set email id here, it may be a range in case you have email id on your worksheet
Mailid = "dunno@hotmail.com"
'Write your email message body here , add more lines using & vbLf _ at the end of each line
Body = "Please find enclosed " & vbLf _
& vbLf _
& "Thanks & Regards"
'Copy Active Sheet and save it to a temporary file
Set cWB = ActiveWorkbook
ActiveSheet.Copy
Set tWB = ActiveWorkbook
FileName = "Temp.xls" 'You can define the name
FilePath = Environ("TEMP")
On Error Resume Next
Kill FilePath & "\" & FileName
On Error GoTo 0
Application.DisplayAlerts = False
tWB.SaveAs FileName:=FilePath & "\" & FileName, FileFormat:=56
Application.DisplayAlerts = True
'Sending email through outlook
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.To = Mailid
.Subject = "Update Message Subject here"
.Body = Body
.Attachments.Add tWB.FullName
.Attachments.Add tWB.FullName
.Display
End With
'Delete the temporary file and restore screen updating
tWB.ChangeFileAccess Mode:=xlReadOnly
Kill tWB.FullName
tWB.Close SaveChanges:=False
cWB.Activate
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub
The email in the codes is not my actual email address for security purpose.
I'm sure there is only a minor configuration to do
Thanks so much inadvance
Bookmarks