|
|
|
date: Thu, 21 Aug 2008 06:55:01 -0700,
group: microsoft.public.access.macros
back
write text from email into table
I've written a macro to pull comma-separated text from emails, which I want
to put into a table
Dim folder As Outlook.MAPIFolder
Dim MailItem As Outlook.MailItem
Dim arr() As String
Dim aaa As String
Set folder = GetNamespace("MAPI").Folders.Item("AAA").Folders.Item("BBB")
For Each MailItem In folder.Items
arr = Split(MailItem.Body, vbCrLf)
For Each aaa in arr
???what do I put here (For Excel, I would shove into cells) ????
Next aaa
Next arr
Set folder = Nothing
Set MailItem = Nothing
Here is a sample of the text
"ABA",0,4616,100.00,13/08/08 12:26
"ABB",0,3669,100.00,13/08/08 12:26
"ABC",470,28417,98.35,13/08/08 12:26
What do I need to do to put the text into a table named KPI53
with fields called
NODE(text),BLOCKS(integer),ATTEMPTS(integer),RATE(decimal),Date(Date/Time)
Do I need to set up a Query (which is alien to me) and other stuff ??
date: Thu, 21 Aug 2008 06:55:01 -0700
author: consort911
Re: write text from email into table
If you can already put it into Excel I would take the easy road and make a
link in Access to that Excel spreadsheet. Then you can run the query you
mentioned that appends the data as you describe. Use the query designer.
Bonnie
http://www.dataplus-svc.com
consort911 wrote:
>I've written a macro to pull comma-separated text from emails, which I want
>to put into a table
>
>Dim folder As Outlook.MAPIFolder
>Dim MailItem As Outlook.MailItem
>Dim arr() As String
>Dim aaa As String
>Set folder = GetNamespace("MAPI").Folders.Item("AAA").Folders.Item("BBB")
>For Each MailItem In folder.Items
> arr = Split(MailItem.Body, vbCrLf)
> For Each aaa in arr
> ???what do I put here (For Excel, I would shove into cells) ????
> Next aaa
>Next arr
>Set folder = Nothing
>Set MailItem = Nothing
>
>Here is a sample of the text
>"ABA",0,4616,100.00,13/08/08 12:26
>"ABB",0,3669,100.00,13/08/08 12:26
>"ABC",470,28417,98.35,13/08/08 12:26
>
>What do I need to do to put the text into a table named KPI53
>with fields called
>NODE(text),BLOCKS(integer),ATTEMPTS(integer),RATE(decimal),Date(Date/Time)
>Do I need to set up a Query (which is alien to me) and other stuff ??
--
Message posted via http://www.accessmonster.com
date: Thu, 21 Aug 2008 14:56:53 GMT
author: bhicks11 via AccessMonster.com u44327@uwe
Re: write text from email into table
I found that I can use an SQL to put it into the table like this
db.Execute ("INSERT INTO KPI53 VALUES (" & aaa & ")")
Although this is a very neat one-liner, easier than using Excel, I wondered
if Access has some other "fancy" way.
I'm not a novice with VB & Excel & SQL, but I am with Access, and I'm
curious if any Access experts can start me off in a different direction.
"bhicks11 via AccessMonster.com" wrote:
> If you can already put it into Excel I would take the easy road and make a
> link in Access to that Excel spreadsheet. Then you can run the query you
> mentioned that appends the data as you describe. Use the query designer.
>
> Bonnie
> http://www.dataplus-svc.com
>
> consort911 wrote:
> >I've written a macro to pull comma-separated text from emails, which I want
> >to put into a table
> >
> >Dim folder As Outlook.MAPIFolder
> >Dim MailItem As Outlook.MailItem
> >Dim arr() As String
> >Dim aaa As String
> >Set folder = GetNamespace("MAPI").Folders.Item("AAA").Folders.Item("BBB")
> >For Each MailItem In folder.Items
> > arr = Split(MailItem.Body, vbCrLf)
> > For Each aaa in arr
> > ???what do I put here (For Excel, I would shove into cells) ????
> > Next aaa
> >Next arr
> >Set folder = Nothing
> >Set MailItem = Nothing
> >
> >Here is a sample of the text
> >"ABA",0,4616,100.00,13/08/08 12:26
> >"ABB",0,3669,100.00,13/08/08 12:26
> >"ABC",470,28417,98.35,13/08/08 12:26
> >
> >What do I need to do to put the text into a table named KPI53
> >with fields called
> >NODE(text),BLOCKS(integer),ATTEMPTS(integer),RATE(decimal),Date(Date/Time)
> >Do I need to set up a Query (which is alien to me) and other stuff ??
>
> --
> Message posted via http://www.accessmonster.com
>
>
date: Thu, 21 Aug 2008 09:29:01 -0700
author: consort911
Re: write text from email into table
wow, that link is very interesting. My first step was to write macros in
Access to "pull" data from Outlook, and my next step was to then write macros
in Outlook to "push" data into Access. That link provides a lot of useful
insights, cheers !!!
"bhicks11 via AccessMonster.com" wrote:
> Very interesting consort911 - I haven't done this but I found a site that
> might help you:
>
> http://blogs.techrepublic.com.com/howdoi/?p=119&tag=rbxccnbtr1
>
> I think this could come in handy and I'm going to fool with it myself.
> Thanks to you!
>
> Bonnie
> http://www.dataplus-svc.com
>
> consort911 wrote:
> >I found that I can use an SQL to put it into the table like this
> >db.Execute ("INSERT INTO KPI53 VALUES (" & aaa & ")")
> >Although this is a very neat one-liner, easier than using Excel, I wondered
> >if Access has some other "fancy" way.
> >
> >I'm not a novice with VB & Excel & SQL, but I am with Access, and I'm
> >curious if any Access experts can start me off in a different direction.
> >
> >> If you can already put it into Excel I would take the easy road and make a
> >> link in Access to that Excel spreadsheet. Then you can run the query you
> >[quoted text clipped - 29 lines]
> >> >NODE(text),BLOCKS(integer),ATTEMPTS(integer),RATE(decimal),Date(Date/Time)
> >> >Do I need to set up a Query (which is alien to me) and other stuff ??
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-macros/200808/1
>
>
date: Thu, 21 Aug 2008 20:37:00 -0700
author: consort911
Re: write text from email into table
Hi Steve, sorry I've got "notify me of replies" ticked, but I'm not getting
email notifications.
Anyway, in Excel & Word & PowerPoint, the macros and the VB modules are one
and the same, whereas in Access, there is a Macros "object" (which I ignore)
and I use the "Modules" object which then resembles the way that Excel is
laid out.
Also, I read a couple of the posts under Programming, but the language being
used did not sound as VB as the language being used in some of the posts here
under Macros. So I think I've made a common mistake by posting in the wrong
sub-section here.
Maybe someone at Microsoft should have been a bit more semantically aware
and called the Macros object something else like "Functions" ??
cheers
Brian H
"Steve Schapel" wrote:
> Consort,
>
> Just a technicality... what you are referring to here is VBA procedures.
> In Access, macros are a quite different beast!
>
> --
> Steve Schapel, Microsoft Access MVP
>
> consort911 wrote:
> > wow, that link is very interesting. My first step was to write macros in
> > Access to "pull" data from Outlook, and my next step was to then write macros
> > in Outlook to "push" data into Access. That link provides a lot of useful
> > insights, cheers !!!
>
date: Mon, 25 Aug 2008 19:50:01 -0700
author: consort911
|
|
