VBA Email - Crash

[j4ymf]

Hello All

only in the past few days I have been experiencing Excel and Outlook crashing when I run this macro.

Sub Email()

    ActiveWorkbook.Save
   Application.Dialogs(xlDialogSendMail).Show _
    arg1:="teamleaders@bennington-foods.co.uk", arg2:="Canning and Pouch Powders"
    
    
End Sub
[\code]

if I comment out the arg1 it works,  please help because I would like it to add the address

many thanks Jason

 

[RedBeard]

Try this then:

Dim strRecipient As String: strRecipient = "email address goes here"
Dim strSubject As String: strSubject = "Subject of email"


Application.Dialogs(xlDialogSendMail).Show Arg1:=strRecipient, Arg2:=strSubject

 

[jez96]

I've had the same issue on a number of our spreadsheets in the office over the last couple of weeks. I'm assuming it's related to either a Microsoft security update, or a Kaspersky update (our anti-virus software). It doesn't however affect all of our PC's running Excel 2013.


I wasn't aware removing the argument allowed it to work so thanks for highlighting this, however declaring the arguments as strings didn't help for me. I seem to remember another Microsoft security update around the same time a couple of years ago which broke VBA on our PC's, so they have good form.


My work around was to save the file first, then send the report by creating an instance of Outlook using late binding. Below is my edited code, you'll probably find cleaner versions elsewhere or have your own:




Dim filePath As String
filePath = "C:\Saved Reports"


Sheets("Sheet1").Copy


ActiveWorkbook.SaveAs Filename:=filePath & "Report Name.xlsx", _
FileFormat:=51, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

ActiveWorkbook.Close SaveChanges:=False


Dim Mail_Object As Object


Set Mail_Object = CreateObject("Outlook.Application")
With Mail_Object.CreateItem(O)
.Subject = "Report Subject"
.To = "someone@somewhere.com"
.Body = "Please see the attached report"
.Attachments.Add filePath & "Report Name.xlsx"
'.send
.Display
End With

Set Mail_Object = Nothing