I have the following code below to attach a sheet from Excel in Outlook
I would like the code mended so that when the attachment is opened, it will be on Cell A1
Your assistance is most appreciated
I would like the code mended so that when the attachment is opened, it will be on Cell A1
Your assistance is most appreciated
Code:
Sub Email_BR1()
Dim File As String, strBody As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
File = Environ$("temp") & "\" & Format(Range("Q2"), "mmm-yy ") & Format(Now, "dd-mmm-yy h-mm-ss") & ".xlsx"
strBody = "Hi " & Sheets("BR1").Range("S2") & vbNewLine & vbNewLine & _
"Attached, please find current " & Sheets("BR1").Range("Q3") & vbNewLine & vbNewLine & _
"Regards" & vbNewLine & vbNewLine & _
"Howard"
ActiveWorkbook.Save
Sheets("BR1").Copy
With ActiveWorkbook
.SaveAs Filename:=File, FileFormat:=51
.Close savechanges:=False
Range("A1").Select
End With
With CreateObject("Outlook.Application").CreateItem(0)
.Display
.to = Join(Application.Transpose(Sheets("BR1").Range("R2:R5").Value), ";")
.Subject = Sheets("BR1").Range("Q3")
.body = strBody
.Attachments.Add File
End With
Kill File
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub