Macro to access Outlook 2007
in [Word VBA]
|
From: Matt on 25 Jun 2008 14:12 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?
From: Graham Mayor on 27 Jun 2008 05:28 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 My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Matt wrote: > 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?
From: Graham Mayor on 27 Jun 2008 08:18 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 My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor wrote: > 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. > > Matt wrote: >> 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?
From: Matt on 27 Jun 2008 11:04 Thank you Graham for taking the time to research this question. I'm going to start testing with code now. "Graham Mayor" wrote: > 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 > > My web site www.gmayor.com > Word MVP web site http://word.mvps.org > <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > > > Graham Mayor wrote: > > 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. > > > > Matt wrote: > >> 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? > > >
From: Matt on 27 Jun 2008 11:26
How do I go about creating the reference? "Graham Mayor" wrote: > 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 > > My web site www.gmayor.com > Word MVP web site http://word.mvps.org > <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > > > Graham Mayor wrote: > > 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. > > > > Matt wrote: > >> 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? > > > |