Macro to send email now fails with run-time error 1004; Mail system failure... Worked for 2 years

BJDibbins

New Member
Joined
Aug 23, 2012
Messages
4
Hi all,

I've been using a macro successfully for the last couple of years, but this morning when I went to use it, it decided to fail. It last worked successfully on the 9th of August 2012.

I have a workbook which contains various spreadsheets. The macro that has failed performs the following tasks:
1. It copies a list of email addresss from an external workbook to a sheet in the current workbook (still works)
2. It copies the referral sheet I want to send to a new file, and saves it with an appropriate filename to an appropriate folder (still works)
3. The macro then creates an email with a standard subject line, attaches the new worksheet and emails it to each of the addresses as above (broken)

The error message is from Microsoft Visual Basic. "Run-time error '1004': Mail system failure. Check your mail installation."

I'm guessing there is a setting somewhere in Excel that has changed as part of an update.

I've been through a number of the options in the developer menu to remove any obvious restrictions (& reopened excel afterwards), but so far it hasn't resolved the problem.

For what it's worth, here is the macro code.

Code:
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    Run "PullInSheet1"
    Dim oldbook As String
        oldbook = ActiveWorkbook.Name
    Dim rlist As Variant
        rlist = Sheets("MasterList").Range("ATC_Email_List")
    Dim subjtext As String
        subjtext = Worksheets("MasterList").Range("ATCRefs_Email_Subject").Value
    Dim sfName As String
        sfName = Worksheets("ATC Ref").Range("ATC_SaveFileName").Value
    Dim sFileName As String
        sFileName = Worksheets("MasterList").Range("ATCRefs_Folder").Value & sfName & "_" & Format(Now, "dd-mmm-yy h-mm") & ".xls"
    Range("ATC_Ref").Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Name = ActiveSheet.Range("A1")
    ActiveWorkbook.SaveAs sFileName
    ActiveWorkbook.SendMail Recipients:=rlist, Subject:=subjtext
    ActiveWorkbook.Close SaveChanges:=False
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

In case you're wondering about the pullinsheet code, I'll add it below - but I probably grabbed it from this forum a couple of years ago (like some of the above) & just made some changes.

Code:
Sub PullInSheet1()
    Dim AreaAddress As String
    '''''' Sheet11.UsedRange.Clear
    Dim ClRange As String
    ClRange = "= 'L:\ADMIN\EMPLOY SERVICES\" _
    & "[MasterList.xls]Sheet2'!RC"
    Sheet11.Cells(1, 1) = ClRange
    'Pass the area Address to a String
    AreaAddress = Sheet11.Cells(1, 1)
    With Sheet11.Range(AreaAddress)
        'If the cell in Sheet1 of the closed workbook is not _
            empty the pull in it's content, else put in an Error.
        .FormulaR1C1 = "=IF('L:\ADMIN\EMPLOY SERVICES\" _
            & "[MasterList.xls]MasterList'!RC="""",NA(),'L:\ADMIN\EMPLOY SERVICES\" _
                & "[MasterList.xls]Masterlist'!RC)"
        'Delete all Error cells
        On Error Resume Next
        .SpecialCells(xlCellTypeFormulas, xlErrors).Clear
        On Error GoTo 0
        'Change all formulas to Values only
        .Value = .Value
    End With
End Sub

There are a couple of things I've wanted to do to improve the macro, but I couldn't justify the need to spend time working it out (since writing spreadsheets isn't really my job). Since it's broken at the moment, I can...

1. I'd like to create a subject line that reflects the name of the person being referred. For some reason though, anything other than text in the cell reference caused an error for me. eg, I tried using concatenate to create my subject line, but it didn't work.

2. I'd like the copied sheet to contain all of the formatting of the original sheet. Presumably there is a paste option that will do this and I just picked the wrong one.

If anyone can help with either of the two improvements, that would be helpful & appreciated, but really my biggest problem is getting the macro to work again.

The mailsystem we use is Groupwise 8. I couldn't find any settings in that program that have been changed, or that I could change.

Any suggestions appreciated.

Kind regards,

Brian

Edits.

1. Yes, I should change the extension from xls to xlsx in the code since I'm using Excel 2010 (but changing it doesn't fix the problem)
 
Last edited:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

BJDibbins

New Member
Joined
Aug 23, 2012
Messages
4
Hi all,

I've resolved the problem. For some reason Groupwise was no longer set as the default mail program. Using control panel & setting the program defaults to ensure that mapi send mail defaults were set fixed the issue.

Now if anyone could help with the other two items (subject line and formatting) I'd still appreciate your help.

Kind regards,

Brian
 

ruff

New Member
Joined
Jan 19, 2012
Messages
10
Try using this,

Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _

Instead of the below in your macro

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
 

BJDibbins

New Member
Joined
Aug 23, 2012
Messages
4
Hmm, I may have spoken too soon. I disabled the sendmail section while testing, and when I enabled it again the macro failed, as instead of the filename in the copied cell a1, it contained the formula instead. The formatting didn't appear in the copied sheet either, so I can only assume I was looking at my original sheet the first time I tried it. Oh well, that will teach me to play with VB code before my first morning coffee.

Thanks for replying though.

Brian
 

Forum statistics

Threads
1,141,125
Messages
5,704,440
Members
421,349
Latest member
Santhosh3188

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top