Hello!
Our company IT Department recently upgraded all of our computers to Windows 2010 and now one of my Excel macros is not working properly. The exact error I'm getting is:
Run-time error '-2147417851 (80010105)':
Method 'To' of object'_MailItem' failed
When I click to debug the problem, it highlights a piece of code that is supposed to grab a "mail to" email address from a specific cell in the same workbook.
Here's the entire macro for context with the problem code bolded and italicized:
Sub GS_Save_and_Send_PDF()
' Saves & emails Greensheet insertion orders
' Looks in the Data Key tab for the sheet names in Column X to select/activate (will only
' select ones with a "Y" in the Save column, which correlates to the visible tabs only)
Dim ShtGroup() As String
Dim Lr As Long
Dim ShtName As String
Dim n As Long
Lr = Sheets("Data Key").Range("X" & Rows.Count).End(xlUp).Row
For Each c In Sheets("Data Key").Range("X25:X" & Lr)
If UCase(c.Offset(, 1)) = "Y" Then
ShtName = c.Value
n = n + 1
ReDim Preserve ShtGroup(1 To n)
ShtGroup(n) = ShtName
End If
Next
Sheets(ShtGroup).Select
' Saves all active (selected) sheets to a single PDF file at the location and filename shown below
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="S:\Marketing\Department Files and Folders\Traditional Media\Print\Insertion Orders\" & ThisWorkbook.Sheets("Management").Range("F8").Value & "\" & ThisWorkbook.Sheets("Management").Range("D8").Value & " " & ThisWorkbook.Sheets("Management").Range("F8").Value & " Greensheet Insertion Order" & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
MsgBox "IMPORTANT: Don't forget to attach the artwork before sending the insertion order!"
' Creates new Outlook email message to the GS rep with appropriate subject and the PDF insertion
' order attached; simply add a personal message and hit send!
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim myAttachments As Object
Set OutLookApp = CreateObject("Outlook.application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
Set myAttachments = OutLookMailItem.Attachments
With OutLookMailItem
.To = ThisWorkbook.Sheets("Data Key").Range("AG19")
.CC = ThisWorkbook.Sheets("Data Key").Range("AG20") & ", " & ThisWorkbook.Sheets("Data Key").Range("AG21")
.Subject = ThisWorkbook.Sheets("Management").Range("D8").Value & " " & ThisWorkbook.Sheets("Management").Range("F8").Value & " Insertion Order for Greensheet"
.body = ""
myAttachments.Add "S:\Marketing\Department Files and Folders\Traditional Media\Print\Insertion Orders\" & ThisWorkbook.Sheets("Management").Range("F8").Value & "\" & ThisWorkbook.Sheets("Management").Range("D8").Value & " " & ThisWorkbook.Sheets("Management").Range("F8").Value & " Greensheet Insertion Order" & ".pdf"
.Display
End With
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
' Goes back to the Management tab and places the cursor in cell A1
Sheets("Management").Select
Range("A1").Select
End Sub
As far as I can tell, everything else is working correctly up until the point where the mail item fails.
I had read on another site that perhaps some of the code references need to be updated due to the OS upgrade, but I'm not even sure where to begin to look at or update that.
Any help would be appreciated!
Thanks!
P.S. - My macro may look fancy to some, but I'm very much still a beginner to VBA...most of what I have was copy/pasted from online pros like you guys! (Thanks for that, by the way!)