Need help! VBA - Error 462 killing me!

hellfire45

Active Member
Joined
Jun 7, 2014
Messages
451
I'm sorry, I posted this yesterday but didn't get an answer so I thought I'd try again. The line in bold is giving me "run-time error 462 - The remote server machine does not exist or is unavailable". I have looked online but I can't understand the solutions. I'm not strong on Outlook VBA and really can't understand how to fix it. Can anybody please please please suggest a possible solution?

Thank you!

Code:
Sub SendReport_loop()

Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object
Dim PromoSheet As String
Dim SigString As String
Dim Signature As String
Dim strPath As String
Dim objOutlookMsg As Object
Dim rng As Range
Dim rebateval As String
Dim strbody As String
Dim ship_table_header As Integer
Dim ship_table_emails As Integer
Dim ship_Table_End As Integer
Dim i As Integer
Dim srcpath As String
Dim reportfolder As String
Dim reportname As String
Dim xlist As Integer


FileExtStr = ".xls"
FileFormatNum = xlExcel8


ship_table_header = Application.Match("Account", Sheet12.Columns(1), 0)
ship_table_emails = Application.Match("Emails", Sheet12.Rows(ship_table_header), 0)
ship_Table_End = Sheet12.Columns(1).Find("*", , xlValues, , xlRows, xlPrevious).Row


i = 0
srcpath = "I:\EndDevTools\Lenovo\Lenovo Retail\CPFR Automation\Lenovo Huge CPFR File\"
Do 'This loop will find the most recent report within the last month
                        If Dir(srcpath & Format(Date - i, "mm-dd-yyyy"), vbDirectory) <> "" Then
                                    reportfolder = srcpath & Format(Date - i, "mm-dd-yyyy") & "\"
                                    Exit Do
                            Else
                                    i = i + 1
                        End If
            Loop Until i = 35
            
            For xlist = ship_table_header + 1 To ship_Table_End
            Application.StatusBar = "Processing Customer Sheet " & ship_table_header - (ship_table_header - 1) & " of " & ship_Table_End - (ship_table_header - 1)
            reportname = reportfolder & Sheet12.Cells(xlist, 1).value & Format(Date - i, "yyyy-mm-dd") & ".xlsx"
            
            
            Set wb = ActiveWorkbook
            
            strbody = "<HTML>******>"
                strbody = strbody & "<font face =""Calibri"" size=""3"">" & "Good Day," & "<br>" & "<br>" _
                        & "Please see the attached " & Sheet12.Cells(xlist, 4).value & " CPFR Data Sheet for the week of " & Format(Date, "mm.dd.yy") & "." & " Please let me know if you have any questions." & "<br>" & "<br>"
            
            strbody = strbody & "</BODY></HTML>"
            Set OutApp = CreateObject("Outlook.Application")
[B]            Set objOutlookMsg = OutApp.CreateItem(olMailItem)[/B]
            Set objOutlookMsg = OutApp.CreateItem(0)
                With objOutlookMsg
                            .Display
                End With
                Signature = objOutlookMsg.Body
                
                With wb
                    With objOutlookMsg
                        .To = Sheet12.Cells(xlist, ship_table_emails).value
            '            .CC = Sheets("DistiList").Range("B7").value
            '            .BCC = Sheets("DistiList").Range("B8").value
                        .Subject = "CPFR Data Sheet - " & Sheet12.Cells(xlist, 4).value & " - " & Format(Date - i, "mm-dd-yyyy")
                        .HTMLBody = strbody & objOutlookMsg.HTMLBody
                        .Attachments.Add (reportname)
                        .Send
                    End With
                End With
                
              
Next xlist
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Jambi46n2

Board Regular
Joined
May 24, 2016
Messages
243
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
hmm..

I haven't tested this but I would experiment with replacing:

Code:
[B]Set objOutlookMsg = OutApp.CreateItem(olMailItem)[/B]

with

Code:
[B]Set [/B][B]objOutlookMsg [/B][B]= [COLOR=#ff0000]OutMail[/COLOR].CreateItem(olMailItem)[/B]
 
Last edited:

hellfire45

Active Member
Joined
Jun 7, 2014
Messages
451
hmm..

I haven't tested this but I would experiment with replacing:

Code:
[B]Set objOutlookMsg = OutApp.CreateItem(olMailItem)[/B]

with

Code:
[B]Set [/B][B]objOutlookMsg [/B][B]= [COLOR=#ff0000]OutMail[/COLOR].CreateItem(olMailItem)[/B]

Thank
you for the effort but this did not fix the issue. :(
 

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,790
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
You appear to be creating outapp as a create outlook object inside your loop
 

Jambi46n2

Board Regular
Joined
May 24, 2016
Messages
243
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Lastly I'd try changing this:

Dim objOutlookMsg As Object

To This:

Dim objOutlookMsg As Range

Or maybe even:

Dim objOutlookMsg As Variant
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,133,538
Messages
5,659,384
Members
418,500
Latest member
Guru Prasad S

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