Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Upgraded to Windows 10 and now Excel Macros Don't Work

0

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!)

Answer
Discuss

Discussion

Just to make sure, is Outlook still installed on the computer?
don (rep: 1989) Sep 14, '16 at 9:00 pm
Yes, Outlook is still installed on the computer. It even generates the new mail window in Outlook, but then gets stuck on who to mail it to.
sday Sep 15, '16 at 11:41 am
Add to Discussion

Answers

0
Selected Answer

Aha! I figured it out!

First, I tried replacing the reference with the actual email address and the macro ran perfectly. However, I still needed to be able to dynamically change the address without having to edit the macro, so I took another look at it and noticed the reference for the Subject line included a ".Value" at the end, so I updated the To code to...

ThisWorkbook.Sheets("Data Key").Range("AG19").Value

...and lo and behold it worked!! :-)

Discuss

Discussion

haha can't believe I missed that! Always the tiny things that get ya ;)
don (rep: 1989) Sep 20, '16 at 1:36 pm
Always!! Thanks for taking a look! :-)
sday Sep 20, '16 at 1:38 pm
Add to Discussion
0

Try changing the ThisWorkbook in this line:

ThisWorkbook.Sheets("Data Key").Range("AG19")

to the actual name of your workbook. It sounds to me like the code is just getting confused about where the data is.

This result would be something like this:

Workbooks("your_file.xls").Sheets("Data Key").Range("AG19")
Discuss

Discussion

Thanks, Don. I don't think that's the issue though. I tried updating that part of the code as you suggested and got the same error as before.

Plus, if you look at the step earlier in the macro that "' Saves all active (selected) sheets to a single PDF file at the location and filename shown below", it uses this same reference structure...

ThisWorkbook.Sheets("Data Key").Range("AG19")


...to determine the filename and that part of the macro still works...

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", _
sday Sep 20, '16 at 12:37 pm
Yea but the pdf section happened before you created the Outlook object. Is there any way you can include a sample workbook?
don (rep: 1989) Sep 20, '16 at 1:28 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login