?
Solved

Extracting email addresses from the body of emails

Posted on 2003-04-01
20
Medium Priority
?
338 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:omb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 9
20 Comments
 
LVL 3

Author Comment

by:omb
ID: 8247941
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
 
LVL 4

Expert Comment

by:mesch
ID: 8252604
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
 
LVL 3

Author Comment

by:omb
ID: 8254353
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 4

Expert Comment

by:mesch
ID: 8255695
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
 
LVL 3

Author Comment

by:omb
ID: 8262489
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
 
LVL 4

Accepted Solution

by:
mesch earned 140 total points
ID: 8263437
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
 
LVL 3

Author Comment

by:omb
ID: 8264100
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
 
LVL 4

Expert Comment

by:mesch
ID: 8264155
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
 
LVL 3

Author Comment

by:omb
ID: 8268200
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
 
LVL 4

Expert Comment

by:mesch
ID: 8268543
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
 
LVL 3

Author Comment

by:omb
ID: 8269457
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
 
LVL 4

Expert Comment

by:mesch
ID: 8269804
don't understand what you mean.

what's standing behind

.Email1Address = ???

when script create contacts with transid?

</nobert>
0
 
LVL 3

Author Comment

by:omb
ID: 8270108
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
 
LVL 4

Expert Comment

by:mesch
ID: 8270431
No - sorry. :-(

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

thanks

</norbert>
0
 
LVL 3

Author Comment

by:omb
ID: 8270721
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
 
LVL 4

Expert Comment

by:mesch
ID: 8270829
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
 
LVL 3

Author Comment

by:omb
ID: 8270949
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
 
LVL 3

Author Comment

by:omb
ID: 8293894
Hi there mesch... any luck with the above? Thanks for your help :)
0
 
LVL 4

Expert Comment

by:mesch
ID: 8295218
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
 
LVL 3

Author Comment

by:omb
ID: 8297968
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Read this checklist to learn more about the 15 things you should never include in an email signature.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question