Extracting email addresses from the body of emails

I use Outlook 2002/XP. I have a web form which sends me an email with lots of useful information which I collect within Outlook. One of the important bits of info I need to extract from these emails is the email address of contacts.

Basically, the web form sends me emails in a strict format (each field on the form has a particular name, which is then sent in email format to me). Hence, the line on which the email address is held is named 'email#:'. Is there a way I can exact these email addresses from my emails by running a script, macro or whatever?

Many thanks for your help.
LVL 3
ombAsked:
Who is Participating?
 
meschConnect With a Mentor Commented:
Okay - this is only a rudimentary script written in an outlook form.

you need two commandbuttons which a named 'cmdPickFolders' and 'cmdCreateContacts'.

With the cmdPickFolders button you should select a first the source folder and in second step the destination contact folder.

with the cmdCreateContacts button you can create the contact for each item in the source folder.

don't forget to change my test subject in the restrict method into your subject from your incoming webform.

~-~-~-~-~-~- ONLY A QAD SAMPLE ~-~-~-~-~-~-

Option Explicit
'-- constant declaration
Const cEmail = "email#:"

'-- public variable declaration
Dim oNS
Dim oSourceFolder
Dim oTargetFolder


Function Item_Open()
'Set reference to MAPI NameSpace
Set oNS = Application.GetNameSpace("MAPI")
If oNS Is Nothing Then
  MsgBox "Namespace object could not be created." & vbLF & "Process canceled!", vbCritical, "Error creating NameSpace"
  Item.Close
End If
End Function

Function Item_Close()

Set oSourceFolder = Nothing
Set oTargetFolder = Nothing
Set oNS = Nothing

End Function

Sub cmdPickFolders_Click()
'Set reference to source mail folder where the webform mails with address data are stored
Set oSourceFolder = oNS.PickFolder
If oSourceFolder.DefaultMessageClass <> "IPM.Note" Then
  MsgBox "Selected folder is not a mail folder!" & vbLF & vbLF & "Try again...", vbExclamation, "No MailFolder"
  Set oSourceFolder = Nothing
  Exit Sub
End If
'Set reference to target contact folder
Set oTargetFolder = oNS.PickFolder
If oTargetFolder.DefaultMessageClass <> "IPM.Contact" Then
  MsgBox "Selected folder is not a contact folder!" & vbLF & vbLF & "Try again...", vbExclamation, "No ContactFolder"
  Set oTargetFolder = Nothing
  Exit Sub
End If
End Sub


Sub cmdCreateContacts_Click()
Dim oItems, oItem, oNewContact
Dim intStart, intEnd
If oSourceFolder Is Nothing OR oTargetFolder Is Nothing Then
  MsgBox "You didn't select Source or/and TargetFolder!", vbCritical, "Error - No Source/TargetFolder"
  Exit Sub
End If
Set oItems = oSourceFolder.Items.Restrict("[Subject] = 'Test mit einer HMTL-Nachricht'")
If oItems Is Nothing Then
  MsgBox "No WebForm items found in Folder " & oSourceFolder.Name & ".", vbInformation, "No items found"
  Exit Sub
Else
  For Each oItem In oItems
    intStart = 0
    intEnd = 0
    intStart = InStr(1,oItem.Body,cEmail) + Len(cEmail) + 1
    intEnd = InStr(intStart, oItem.Body, vbLF)
(oItem.Body, intStart, intEnd - intStart)
intEnd - intStart)
    Set oNewContact = oTargetFolder.Items.Add
    With oNewContact
      .Email1Address = Mid(oItem.Body, intStart, intEnd - intStart)
      .Save
    End With
    Set oNewContact = Nothing
  Next
End If
    '-- Clear Memory
Set oItems = Nothing
Set oItem = Nothing
End Sub

~-~-~-~-~-~- END OF SAMPLE ~-~-~-~-~-~-
   
</norbert>

0
 
ombAuthor Commented:
Just a couple of other things to point out:

1. I would like to run this script on particular Outlook folders of my choice.

2. The emails themselves are in HTML format.
0
 
meschCommented:
Hey omb;

okay to run the script on an Outlook folder of your choice you should take a look a Sue sample on www.slipstick.com homepage:

http://www.slipstick.com/dev/code/getfolder.htm

To get the email address from the item body you can look at the following sample.

Option Explicit

Const olFolderInbox = 6
Const cEmail = "email#:"

Sub CommandButton1_Click()
Dim oNS, oInbox, oItem
Dim intStart, intEnd
Set oNS = Application.GetNameSpace("MAPI")
Set oInbox = oNS.GetDefaultFolder(olFolderInbox)
Set oItem = oInbox.Items.Find("[Subject] = 'WebContact'")
intStart = InStr(1,oItem.Body,cEmail) + Len(cEmail) + 1 ' + 1 for the space between ":" and Emailaddress
intEnd = InStr(intStart,oItem.Body," ")
MsgBox "Email address: " & Mid(oItem.Body, intStart, intEnd - intStart)
'-- Clear Memory
Set oItem = Nothing
Set oInbox = Nothing
Set oNS = Nothing
End Sub

This sample demonstrates how to get the email address from the email body property. The sample tracks only the first mail item with the subject "WebContact" in the Outlook inbox.
</norbert>
0
Easily manage email signatures in Office 365

Managing email signatures in Office 365 can be a challenging task if you don't have the right tool. CodeTwo Email Signatures for Office 365 will help you implement a unified email signature look, no matter what email client is used by users. Test it for free!

 
ombAuthor Commented:
Thanks mesch. I kind of got it to work - however, the script only displays the first email address in the first email it finds within the folder. It also displays the name of the 2nd field name on the next line below. An example of the msg box:

Email address: joe.bloggs@hotmail.com
address1#:

Thinking about it more logically, it is possible to exact these email addresses and instead of displaying them in a msgbox, insert them into a contacts folder which I have newly created? What I want to do is exact these email addresses so that I can use them.

Thanks again
0
 
meschCommented:
Okay you should replace the space character through vbLF.

No problem to think more logical. Do you want to get a complete solution? You can create a form where you type in the folderpath in a textbox or use the pickfolder method.

how can you detect the email. is it a unique subject? you can user the restrict method instead of using find (that should only be a sample). The restrict method on the items collection of a folder returns a items collection with all items with the right subject.
You can loop through these items with a for each loop. Creating a contact item is easy as well. set a reference to the contact folder an use the CreateItem method to create a new contact.

Set myNameSpace = Application.GetNameSpace("MAPI")
Set myFolder = myNameSpace.GetDefaultFolder(10)
Set myItem = Application.CreateItem(2)
myItem.Display

</norbert>
0
 
ombAuthor Commented:
Thanks mesch. Got the vbLF bit to work using:

intEnd = InStr(intStart, oItem.Body, vbLf)

However, you lost me with the next bit (this is the first time I've used VB!). Not really sure what the 'myNameSpace', 'myFolder' etc. and the 'GetDefaultFolder(10)', 'CreateItem(2)' bits mean?!

Each email has the same subject and is sent from the same email address. I've created a new contact folder called contact1 - I would like the email addresses incerted into this folder.

Thanks for your help and patience with a VB virgin!
0
 
ombAuthor Commented:
Wow mesch - you're the man!

Just before I put your script to the test, what type of form should I 'choose' or 'design'? Finally, using the 'control toolbox' I can add a 'commandbutton', do I then use 'Script Editor' to input your script (form/view code)?

Can't wait to put it to the test, thanks so much for all your help (and patience).
0
 
meschCommented:
Take a simple mail form (IPM.Note) create the two commandbuttons and paste the code into the scripteditor. run the form and test it.

</norbert>
0
 
ombAuthor Commented:
Sooo close.....

I've created the form and tested. I got a "Script error", 'Expected statement Line No:63". I believe this refers to the line:

(oItem.Body, intStart, intEnd - intStart)
intEnd - intStart)

Anyway, I removed the above and ran the script. All worked just fine. Using the 'cmdPickFolders' button I was able to select folder which contains webform emails and using the 'cmdCreateContacts' buttons, email address were input into my new contacts folder. But...

...the email addresses were infact the first contains of field1 within the webform. The webform has many fields. The 1st field is called "transID:", and it's value was input as an email address of the contact folder. This probably has something to do with the script error?

I'm sure the nut is almost cracked :)
0
 
meschCommented:
Hey omb;

I will not say I give up, but... do you have any experience in programming? ;-)

If you remove the Mid() function

.Email1Address = Mid(oItem.Body, intStart, intEnd - intStart)

you will not get the addresses. In this function you write the smtp address into the email1address property of the outlook contact form.

The script was only a sample to show you how to do that. If you are not able to debug the script use some message boxes to look for the error.

The constants declared at the beginning of the script is used in the instr-function to find the string email#:

May be you should post a complete sample of you incoming webform mail.
</norbert>
0
 
ombAuthor Commented:
Hi mesch

You're right - I have absolutely no programming experience, I thought this would be an easy script to just cut/paste and then run!

As you suggest, a sample of my webform email:

From: form@mydomain.com
Subject: Application Form

The body includes fields such as...

transId:  12345678
for:  Company Name
phone#:  +12 1234 5678
fax-no#:  +12 345 678
e-mail#:  joe.bloggs@test.com

I want to extract the "e-mail#:" field (i.e. joe.bloggs@test.com) and insert this into a contacts folder.

I didn't remove the Mid() function. But with the lines...

(oItem.Body, intStart, intEnd - intStart)
intEnd - intStart)

...the script does not run, reporting "Expected statement Line No:63". So I removed them, the script ran, but wrote the 'transId:' value (i.e. 12345678) within the email1address property of the outlook contact form.

I am a total VB/scripting virgin - sorry for your frustration. One last attempt? :)
0
 
meschCommented:
don't understand what you mean.

what's standing behind

.Email1Address = ???

when script create contacts with transid?

</nobert>
0
 
ombAuthor Commented:
When I run the script, it creates a contact for each email within the relevant folder with subject "Application Form". This contact record has it's email field populated with the contents of the 'transid:' field of my webform. eg. If I have 3 webform emails:

transId:  123
for:  Company Name 1
e-mail#:  alias1@test.com

transId:  456
for:  Company Name 2
e-mail#:  alias2@test.com

transId:  789
for:  Company Name 2
e-mail#:  alias2@test.com

Then the script would create 3 contacts with email addresses:

123
456
789

of course, I am after 3 contact records containing:

alias1@test.com
alias2@test.com
alias3@test.com

Hope this better explains it...
0
 
meschCommented:
No - sorry. :-(

Please post the whole script code that returns the above result.

thanks

</norbert>
0
 
ombAuthor Commented:
Here goes...

Option Explicit
'-- constant declaration
Const cEmail = "email#:"

'-- public variable declaration
Dim oNS
Dim oSourceFolder
Dim oTargetFolder


Function Item_Open()
'Set reference to MAPI NameSpace
Set oNS = Application.GetNameSpace("MAPI")
If oNS Is Nothing Then
 MsgBox "Namespace object could not be created." & vbLF & "Process canceled!", vbCritical, "Error creating NameSpace"
 Item.Close
End If
End Function

Function Item_Close()

Set oSourceFolder = Nothing
Set oTargetFolder = Nothing
Set oNS = Nothing

End Function

Sub cmdPickFolders_Click()
'Set reference to source mail folder where the webform mails with address data are stored
Set oSourceFolder = oNS.PickFolder
If oSourceFolder.DefaultMessageClass <> "IPM.Note" Then
 MsgBox "Selected folder is not a mail folder!" & vbLF & vbLF & "Try again...", vbExclamation, "No MailFolder"
 Set oSourceFolder = Nothing
 Exit Sub
End If
'Set reference to target contact folder
Set oTargetFolder = oNS.PickFolder
If oTargetFolder.DefaultMessageClass <> "IPM.Contact" Then
 MsgBox "Selected folder is not a contact folder!" & vbLF & vbLF & "Try again...", vbExclamation, "No ContactFolder"
 Set oTargetFolder = Nothing
 Exit Sub
End If
End Sub


Sub cmdCreateContacts_Click()
Dim oItems, oItem, oNewContact
Dim intStart, intEnd
If oSourceFolder Is Nothing OR oTargetFolder Is Nothing Then
 MsgBox "You didn't select Source or/and TargetFolder!", vbCritical, "Error - No Source/TargetFolder"
 Exit Sub
End If
Set oItems = oSourceFolder.Items.Restrict("[Subject] = 'Application Form'")
If oItems Is Nothing Then
 MsgBox "No WebForm items found in Folder " & oSourceFolder.Name & ".", vbInformation, "No items found"
 Exit Sub
Else
 For Each oItem In oItems
   intStart = 0
   intEnd = 0
   intStart = InStr(1,oItem.Body,cEmail) + Len(cEmail) + 1
   intEnd = InStr(intStart, oItem.Body, vbLF)
   Set oNewContact = oTargetFolder.Items.Add
   With oNewContact
     .Email1Address = Mid(oItem.Body, intStart, intEnd - intStart)
     .Save
   End With
   Set oNewContact = Nothing
 Next
End If
   '-- Clear Memory
Set oItems = Nothing
Set oItem = Nothing
End Sub
0
 
meschCommented:
insert a new line after....


intStart = InStr(1,oItem.Body,cEmail) + Len(cEmail) + 1
intEnd = InStr(intStart, oItem.Body, vbLF)
 
MsgBox "Startposition: " & intStart & vbLF & "EndPosition: " & intEnd
MsgBox Mid(oItem.Body, intStart, (intEnd - intStart))

What are the results?
</norbert>
0
 
ombAuthor Commented:
Having inserted your script, now, when I click the 'commandButton2' I get a VBScript msgbox:

StartPosition: 10
EndPosition: 21

...and other:

123

...continues with the same Start and End positions but different 'transid' results ie... 345, 678 etc.
0
 
ombAuthor Commented:
Hi there mesch... any luck with the above? Thanks for your help :)
0
 
meschCommented:
Hey omb;

last try! :-)
Look at your sample a especially at the word email!!!
In your sample you write e-mail#: in your first post email#: (without a dash).
Change the constant at the beginning of the script

Const cEmail = "e-mail#:"

</norbert>
0
 
ombAuthor Commented:
Yeeee Haaaa! You're the man! Well done.

Yep, the constant needs to be e-mail with a dash (e-mail#:) and not as I initially posted it without (email#:). Sorry for the oversight and thanks v.much for all your patience.

FULL MARKS...
0
All Courses

From novice to tech pro — start learning today.