hi guys
I have a script which automatically sends an email with the workbook attached to people after a macro has been run. The problem I am now facing is that I want the file attachment to be attached as a xlxs file so when they open the workbook the macro will not run again for the people that open the file from the email.
here is the code I am using
I have a script which automatically sends an email with the workbook attached to people after a macro has been run. The problem I am now facing is that I want the file attachment to be attached as a xlxs file so when they open the workbook the macro will not run again for the people that open the file from the email.
here is the code I am using
Code:
[COLOR=#222222][FONT=Verdana]Sub email()[/FONT][/COLOR][/FONT][/COLOR]
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim OutMail As Object
Dim tempFilename2
Set wb1 = ActiveWorkbook
If Val(Application.Version) >= 12 Then
If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file. There will" & vbNewLine & _
"be no VBA code in the file you send. Save the" & vbNewLine & _
"file as a macro-enabled (.xlsx) and then retry the macro.", vbInformation
Exit Sub
End If
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
' Make a copy of the file.
' If you want to change the file name then change only TempFileName variable.
TempFilePath = Environ$("temp") & "\"
TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
FileExtStr = "." & LCase(Right(wb1.Name, _
Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))
wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
' Change the mail address and subject in the macro before you run this procedure.
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Estates Data Pull"
.Body = "Hi felix here is today's data results"
.Attachments.Add wb2.FullName
.Send
End With
On Error GoTo 0
wb2.Close SaveChanges:=False
End Sub
[COLOR=#000000][FONT=Arial]