Macro to access Outlook 2007

[Matt]

Hello,
Ok, so here's my issue. Do to the security settings in my office, Outlook
2007 is set up so you can not write any VBA code or create any macros. But I
have this report that is made up every morning and I have began the processes
of trying to automate this report in Word 2003, and in this doc I have
calculated fields, linked excel charts/graphs, etc... .
But a couple tables that are in this report are emailed to me every morning.
The emails come as an attachment which opens up IE and displays a table. So
what I'm trying to do(if possible), is to use VB in word to pullOut/extract
or whatever the correct terminology is and place that table into word.
Possible?

 

[Graham Mayor]

I think we have been here before?

I don't know enough about the Outlook type library for Word vba to know if
it is possible to open an e-mail message in Outlook, from Word and save (or
open) the attachment, which is presumably a Word document? I cannot find any
useful code samples on the web that might help. If Tony Jollans is watching,
he might know, but he has been absent from the forum for a couple of weeks -
maybe on holiday?

If this is not possible you will have the extra step of manually saving the
attachment.

Once you get that far it should be possible to copy the required content
from one document to another - especially if the e-mailed document is
identically laid out. It is just a matter of selecting the required part(s)
and copying them to the other document. How straightforward that would be
would rather depend on how the document is laid out, what it is you want to
extract, and where you want to put it in the other document.

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


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

 

[Graham Mayor]

Thanks to fellow MVPs Peter Jamieson & Doug Robbins, we can now take this a
step further. If you add a reference to the Oulook Object library you could
use the following Word macro to save the attachment to file. Here the e-mail
is selected from an inbox sub folder A_Test which is was using for testing,
to a folder path set at strFolder

Sub ExtractOutlookAttachment()
Dim l As Long
Dim oa As Outlook.Application
Dim ns As Outlook.Namespace
Dim fn As Outlook.Folder
Dim mm As Outlook.MailItem
Dim at As Outlook.Attachment
Dim strFolder As String

strFolder = "D:\My Documents\Test\Versions\Odd\Outlook\"
On Error Resume Next
Set oa = GetObject(, "Outlook.Application")
If Err = 429 Then
Set oa = CreateObject("Outlook.Application")
End If

Set ns = oa.GetNamespace("MAPI")
Set fn = ns.GetDefaultFolder(olFolderInbox).Folders("A_Test")
For l = 1 To fn.Items.Count
Set mm = fn.Items(1)
If mm.Attachments.Count > 0 Then
Set at = mm.Attachments(1)
at.SaveAsFile strFolder & at.FileName
Set at = Nothing
Exit For
End If
Next
Set mm = Nothing
Set fn = Nothing
Set ns = Nothing
Set oa = Nothing
End Sub

Having grabbed the document (and if your attachment always has the same
name, the macro will overwrite the previous copy without prompting) you can
extract the data you want from it to your report. The previous thread were
we discussed working in Outlook vba, should give you some ideas on
techniques for copying between documents.

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


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

 

[Matt]

Thank you Graham for taking the time to research this question. I'm going to
start testing with code now.

 

[Matt]

How do I go about creating the reference?

Thanks to fellow MVPs Peter Jamieson & Doug Robbins, we can now take this a
step further. If you add a reference to the Oulook Object library you could
use the following Word macro to save the attachment to file. Here the e-mail
is selected from an inbox sub folder A_Test which is was using for testing,
to a folder path set at strFolder

Sub ExtractOutlookAttachment()
Dim l As Long
Dim oa As Outlook.Application
Dim ns As Outlook.Namespace
Dim fn As Outlook.Folder
Dim mm As Outlook.MailItem
Dim at As Outlook.Attachment
Dim strFolder As String

strFolder = "D:\My Documents\Test\Versions\Odd\Outlook\"
On Error Resume Next
Set oa = GetObject(, "Outlook.Application")
If Err = 429 Then
Set oa = CreateObject("Outlook.Application")
End If

Set ns = oa.GetNamespace("MAPI")
Set fn = ns.GetDefaultFolder(olFolderInbox).Folders("A_Test")
For l = 1 To fn.Items.Count
Set mm = fn.Items(1)
If mm.Attachments.Count > 0 Then
Set at = mm.Attachments(1)
at.SaveAsFile strFolder & at.FileName
Set at = Nothing
Exit For
End If
Next
Set mm = Nothing
Set fn = Nothing
Set ns = Nothing
Set oa = Nothing
End Sub

Having grabbed the document (and if your attachment always has the same
name, the macro will overwrite the previous copy without prompting) you can
extract the data you want from it to your report. The previous thread were
we discussed working in Outlook vba, should give you some ideas on
techniques for copying between documents.

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


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

 

[Matt]

Sorry, if you went referencing in word to outlook, I have that reference
already. I have inserted that code in word and insert the location for the
folder. What other parts of that code have to be changed?

 

[Graham Mayor]

If you have added the Outlook reference (vba > tools > references >
Microsoft Outlook 12.0 object library) then the bits of the code that you
need to change to your own requirements are the inbox folder -

Set fn = ns.GetDefaultFolder(olFolderInbox).Folders("A_Test")

Here the folder is a sub folder of Inbox called A-Test. Change the name to
the sub folder you are using.

You would need to create a rule in Outlook to automatically divert your
daily document to the folder.

You can use the Inbox

Set fn = ns.GetDefaultFolder(olFolderInbox)

but I recommend against it, as all incoming mail goes there by default and
you may find yourself working with the wrong attachment

The other part that needs to be changed is the folder where you want the
attached document to be saved i.e.

strFolder = "D:\My Documents\Test\Versions\Odd\Outlook\"

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


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

 

[Graham Mayor]

I have been playing around further with this to use it in my own application
we discussed in an earlier thread. It seems that as originally reproduced it
picks the earliest message in the folder rather than the latest. So I have
modified it to lose the loop and pick the last message. I have also changed
the variable names to something a little more meaningful.

Sub ExtractAttachment()
Dim i As Long
Dim olApp As Outlook.Application
Dim olNs As Outlook.Namespace
Dim olFolder As Outlook.Folder
Dim olItem As Outlook.MailItem
Dim olAtt As Outlook.Attachment
Dim strFolder As String

strFolder = "D:\My Documents\Test\Versions\Odd\Outlook\"
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If Err = 429 Then
Set olApp = CreateObject("Outlook.Application")
End If

Set olNs = olApp.GetNamespace("MAPI")
Set olFolder = olNs.GetDefaultFolder(olFolderInbox).Folders("A_Test")
i = olFolder.Items.Count
Set olItem = olFolder.Items(i)
If olItem.Attachments.Count > 0 Then
Set olAtt = olItem.Attachments(1)
olAtt.SaveAsFile strFolder & olAtt.FileName
Set olAtt = Nothing
End If
Set olItem = Nothing
Set olFolder = Nothing
Set olNs = Nothing
Set olApp = Nothing
End Sub

For my own use, the messages I wish to extract do not have attachments and
the data is in the message body, so for the sake of completeness, I have
modified the above to:

Sub ExtractOLMessage()
Dim i As Long
Dim olApp As Outlook.Application
Dim olNs As Outlook.Namespace
Dim olFolder As Outlook.Folder
Dim olItem As Outlook.MailItem
Dim tempDoc As Document

On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If Err = 429 Then
Set olApp = CreateObject("Outlook.Application")
End If

Set olNs = olApp.GetNamespace("MAPI")
Set olFolder = olNs.GetDefaultFolder(olFolderInbox).Folders("Euro")
i = olFolder.Items.Count
Set olItem = olFolder.Items(i)
Set tempDoc = Documents.Add
tempDoc.Content.InsertAfter olItem.Body
olItem.UnRead = False
Set olItem = Nothing
Set olItem = Nothing
Set olFolder = Nothing
Set olNs = Nothing
Set olApp = Nothing
Call ExtractEUR
End Sub

This calls the macro from the earlier thread to extract the required data
from the temporary document and insert it into my running log.

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


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

 

[Matt]

Hey Graham,
Much success, Thanks for your help!
Another question for you.
Q: Now that I have the one Macro working for the email's with the
attachments I would like to try and do another one that saves the entire
message as an excel file type, (manually I would do, with the message opened
I would do a SaveAs/"Daily Processing Report.xls", and save it to a
particular folder.)
After I save it though, it Saves As "Daily Processing Report.xls.htm" then I
would have to go to where the file is saved, R.click / Properties and then
erase the .htm extenstion so now it would read as "Daily Processing
Report.xls" and now it opens as an Excel file.

 

[Graham Mayor]

Word has no ability to save as Excel.

You could certainly grab the body of the message, as shown in the example I
posted earlier.

You may be able to manipulate the message text to be able to convert it to a
Word table and paste it into an Excel worksheet, or convert it to a comma
delimited format and save as plain text with a csv extension but I would be
guessing at a method without seeing a message - and even then I have no
confidence that I could produce a working example.

If that doesn't suggest a way forwards, whether the macro sample can be
adapted to work in Excel I cannot say and is really off topic for this
forum. Try the Excel programming forum.

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


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

 

[Matt]

Ok, I'll try the Excel forum. Thanks for all your help.

 

[Graham Mayor]

You are welcome

Just a point - if indeed the saved file opens correctly in Excel once the
extension has been removed, you could save yourself that step by putting
quotes around the filename when you save it, then the htm bit will not be
added.

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


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

 

[Matt]

Thanks AGAIN!! Worked perfect.

You are welcome

Just a point - if indeed the saved file opens correctly in Excel once the
extension has been removed, you could save yourself that step by putting
quotes around the filename when you save it, then the htm bit will not be
added.

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


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