Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Importing / Linking / July 2005

Tip: Looking for answers? Try searching our database.

Importing select data from e-mails

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Taz - 26 Jul 2005 16:49 GMT
Is there a way to take specific data from an email and import it into a db in
access? I get pre-formatted customer response e-mails that contain the same
data in the same place in each e-mail:

EXAMPLE:
name: Joe Smith
city: Anytown, USA

I want to take this type of data and put into a table with headings of name,
city, etc.

Can this be done and how?

Thanks.

Taz
Joe Fallon - 28 Jul 2005 02:51 GMT
In a code module, use Tools References to set one for Outlook.
Then modify this code to do what you need.

Public Sub SaveAttachment(strPath As String)
On Error GoTo Err_SaveAttachment

 Dim ol As New Outlook.Application
 Dim ns As Outlook.NameSpace
 Dim MyInbox As Outlook.Items
 Dim fldr As Outlook.MAPIFolder
 Dim itm As Outlook.MailItem
 Dim mFile As String, NumAttachments As Integer, i As Integer, NumEmails As
Integer, strTo As String

 Set ns = ol.GetNamespace("MAPI")
 Set MyInbox = ns.GetDefaultFolder(olFolderInbox).Items

 'set a reference to a folder to move the items to
 Set fldr = ns.Folders("Personal Folders").Folders("Saved
Messages").Folders("Bids")

 For Each itm In MyInbox
   'Debug.Print itm.Subject, itm.To, itm.SenderName
   If itm.Subject Like "*Bid*" Then
     NumAttachments = itm.Attachments.Count
     i = 1  'attachment number
     Do While i <= NumAttachments
       mFile = itm.Attachments.Item(i).filename
       itm.Attachments.Item(i).SaveAsFile strPath & mFile
       i = i + 1
     Loop
   Else
     'Debug.Print "Not a Bid"
   End If
 Next

 'In order to move all messages from one folder to another, you must loop
backwards through the index
 NumEmails = MyInbox.Count
 For i = NumEmails To 1 Step -1
   If MyInbox.Item(i).Subject Like "*Bid*" Then
     Set itm = MyInbox.Item(i)
     'Get e-mail address for the acknowledgment from the Body of the
original message
     strTo = GetAddress(itm.Body)
     Call SendEmailMessage("This is to acknowledge that your Bid has been
received and will be processed shortly.", "This is the body of your message
to us:" & vbCRLF & itm.Body, strTo)
     itm.Move fldr
   End If
 Next i

Exit_SaveAttachment:
 Set itm = Nothing
 Set MyInbox = Nothing
 Set ns = Nothing
 Set ol = Nothing
 Exit Sub

Err_SaveAttachment:
 MsgBox ("Error # " & str(Err.Number) & " was generated by " & Err.Source &
Chr(13) & Err.Description)
 Resume Exit_SaveAttachment

End Sub

Sub SendEmailMessage(strSubject As String, strBody As String, strTo As
String)
On Error GoTo Err_SendEmailMessage
 Dim ol As New Outlook.Application
 Dim ns As Outlook.NameSpace
 Dim newMail As Outlook.MailItem

 Set ns = ol.GetNamespace("MAPI")
 Set newMail = ol.CreateItem(olMailItem)
 With newMail
   .Subject = strSubject
   .Body = strBody & vbCRLF
   With .Recipients.Add(strTo)
     .Type = olTo
   End With
   .Send
 End With

Exit_SendEmailMessage:
 Set ol = Nothing
 Set ns = Nothing
 Set newMail = Nothing
 Exit Sub

Err_SendEmailMessage:
 MsgBox ("Error # " & str(Err.Number) & " was generated by " & Err.Source &
Chr(13) & Err.Description)
 Resume Exit_SendEmailMessage

End Sub

You may need to use a program named ClickYes! in order to use the
SendEmailMessage code.
Oultook security now pops up dialog boxes that you can't program around.
ClickYes! looks for them and "clicks the Yes button" for your code.

http://www.express-soft.com/mailmate/clickyes.html

Signature

Joe Fallon
Access MVP

> Is there a way to take specific data from an email and import it into a db
> in
[quoted text clipped - 15 lines]
>
> Taz
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.