save email message

[matt]

I am trying to create the following sub to save an email message as an Excel
file. The Sub does search through the outlook folder and saves the message as
"Daily Processing Report.xls", but I get an error when trying to open it,
"File Format invalid". I am able to do this manually by opening the message
and doing a file save as and typing "Daily Processing Report.xls" and it
opens just fine. But when I do it manually it also saves a folder with the
same name and in it it has two xml files and one file with .thmx extension. I
want to be able to do this with a macro if possible.

sub extractEmailMessage()

Dim olApp As outlook.Application
Dim olNs As outlook.NameSpace
Dim olFolder As outlook.Folder
Dim olItem As outlook.MailItem

Set olApp = CreateObject("Outlook.Application")
Set olNs = olApp.GetNamespace("MAPI")
Set olFolder = olNs.GetDefaultFolder(olFolderInbox).Folders("Folder")

dirname = "D:\Emails\"

For Each olItem In olFolder.Items
If olItem.Class = olMail Then
olItem.SaveAs dirname & "Daily Status Report.xls"
End If
Next

End Sub

 

[Graham Mayor]

I seem to recall earlier correspondence about this. The reason you get the
extra folder when you save manually is because you are saving as htm. The
fact that you are changing the filename extention doesn't affect that basic
premise. When you save with your macro, and you do not specify the format,
olMSG format is used by default. You need to specify the SaveAS type.

olItem.SaveAs dirname & "Daily Status Report.xls", olHTML

should fix it, but I have not tested it.

Why not simply save the message with its default htm filename? Excel 2007
can open HTM files without problem?

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[matt]

Thanks again Graham, that did it. The reason I just didn't just save it as an
html and just open with excel is because it's used in a report and it's
linked in there. Just trying to save some steps.
THANKS!!!!

 

[matt]

Could you possibly take a look at this post by me "Format Email in body", I
can't figure out why it is blowing up the charts that are put into the
message. I think because there are so many replys in that post no one thought
to take a look at it.
Thanks.

 

[Graham Mayor]

Ok - done


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>