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.
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.
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)
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: