excelvbaoutlookeml

Store name of email attachment - gives error on first run but works on second run

I'm attempting to open a dot .eml file stored locally and access the attachments file name with a excel macro. I've gathered some code that does the job but not really. Opening the .eml file works (Set Myinspect = OL.ActiveInspector), but on the next line (Set MyItem = Myinspect.CurrentItem) I get the error "Run-time error '91' - Object variable or With block variable not set".

However if I re-run the code from the beginning after the first attempt (with the email now open from the last run), I get the name of the attachment without errors, and here naturally the first instance of the email closes and a second instance is opened. If I remove the line "MyItem.Close 1" I will have two instances of the email after the second run.

I suspected this might be due to that the email did not have time to open and load before the code tried to retrieve the name of the attachment, hence I tried to put a MsgBox before setting "Myitem" and wait until the email had loaded but that did not do the trick..

Appreciate any help that can be provided on this. The end use of the code is to loop through a list of .eml files to search for a .eml file with a attachment with a pre-determined name and then return the name of the .eml file, so since it loops a faster solution then "wait 5 seconds" for example would be optimal.

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As 
Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal 
lpDirectory As String, ByVal nShowCmd As Long) As Long

Private Const SW_SHOWNORMAL As Long = 1
Private Const SW_SHOWMAXIMIZED As Long = 3
Private Const SW_SHOWMINIMIZED As Long = 2

Sub test11()
strMyFile = "C:\test1.eml"
Dim Myinspect As Outlook.Inspector
Dim MyItem As Outlook.MailItem
Dim OL As Object

If Dir(strMyFile) = "" Then
    MsgBox "File " & strMyFile & " does not exist"
Else
    ShellExecute 0, "Open", strMyFile, "", "C:\test1.eml", SW_SHOWNORMAL
End If

Set OL = CreateObject("Outlook.Application")
Set Myinspect = OL.ActiveInspector
Set MyItem = Myinspect.CurrentItem
MsgBox "Attachment = " & MyItem.Attachments(1)
MyItem.Close 1

End Sub
Solution

  • Please, try replacing of:

    ShellExecute 0, "Open", strMyFile, "", "C:\test1.eml", SW_SHOWNORMAL

    with

    Const waitOnReturn as boolean = True

VBA.CreateObject("WScript.Shell").Run """" & strMyFile & """", 1, waitOnReturn

    This version will wait for the application to open the file. At least, theoretically...:) And no need of any API.

    Please, send some feedback after testing it.