Link to home
Start Free TrialLog in
Avatar of John Sheehy
John SheehyFlag for United States of America

asked on

Locating text in one field and copying it to another field

I get a lot of Data Loss emails 100-200 a day.  I don't have the time to read every one of them everyday.  (It seems like a lot, but we are tracking everything anybody sends outside of the network that have keywords in the email, and I get them all for only one site right now)  

So I set it up that when I receive an email it is automatically added to the access table DLP_Emails.  This works great copies all the details I need.

However, the message body can be a bit lengthy.

Is there a way in VBA to look at that field, go nine lines down and look to the right 19 characters and copy everything after that (to the right) to it's own field?.

Thanks
John

Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

If it has vbcrlf you could use Split command to create an array out of the text and iterate it and keep what you want
Avatar of John Sheehy

ASKER

John, How would I do that?
The field I am dealing with is body (Long text), the field I want to fill in is Host_Name (Short text)

Thanks.
<<Is there a way in VBA to look at that field, go nine lines down and look to the right 19 characters and copy everything after that (to the right) to it's own field?. >>

Simple enough with the usual string functions; InStr(), Left(), Mid().  "Lines" though depends on the format of the e-mail.   Same is true for spacing.   Counting characters if it is HTML formatted vs plain text would not always work for example.

You'd be better to look for a keyword with InStr().

Jim.
The body field is plain text,
But I am not all that versed in InStr(), Left() and Mid().  I have rarely used them in any of my applications
Any examples would be much appreciated.

Thanks you.
Dim textSplit() as String
TextSplit =split(Email_Text,vbcrlf)
'Now put a watch on the TextSplit and check how it handles lines
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am testing all this out now and will report back shortly.
So after some tinkering this was the solution:
TempHost = Right(Split(Mailobject.Body, vbLf)(20), 11)
So this is good and gets me the info I need.

Thank you all who contributed.