My Assistant
![]() ![]() |
|
|
Mar 12 2012, 04:36 PM
Post
#1
|
|
|
UtterAccess Member Posts: 23 |
Hello!
I was wondering if this is possible or not and where to start. I am familiar with VBA in MS Access but I have not worked with it in Outlook. Is there a way, for a script or macro to search the body of an email for an email address, and if there is one to send an email to that address? That's really the guts of what I am curious about. Thanks for anyone that replies!! |
|
|
|
Mar 12 2012, 04:39 PM
Post
#2
|
|
|
Access Wiki and Forums Moderator Posts: 47,919 From: SoCal, USA |
Hi,
I don't use Outlook VBA but what you want should be doable from within Access. When do you want to "scan" the messages to send the email? If as soon as a message arrives, perhaps a Rule is all you need. Just my 2 cents... (IMG:style_emoticons/default/2cents.gif) |
|
|
|
Mar 12 2012, 04:52 PM
Post
#3
|
|
|
UtterAccess Member Posts: 23 |
I know I can figure out a way to do it in Access, but this person would like for this to be as simple as it can, i.e not have to export data into another program.
The message would need to be scanned when it arrived, search the body of the email for an email address string, return that string in a new email as the "to:". Thanks This post has been edited by crb14: Mar 12 2012, 04:52 PM |
|
|
|
Mar 12 2012, 05:22 PM
Post
#4
|
|
|
Access Wiki and Forums Moderator Posts: 47,919 From: SoCal, USA |
Hi,
It does sounds like you might be able to just use a Rule for this - no need for VBA. Have you looked into that? Just my 2 cents... (IMG:style_emoticons/default/2cents.gif) |
|
|
|
Mar 13 2012, 09:31 AM
Post
#5
|
|
|
UtterAccess Member Posts: 23 |
I've looked into a rule.
I'm not needing to auto reply to the sender, but to an email address located in the body. EXAMPLE EMAIL: From: John Doe To: Jane Doe Subject: Application Body: Name: Johnny Doe Email: johnnydoe@yahoo.com What I am needing is to find johnnydoe@yahoo.com and send a new email automatically. I believe this would require VBA. |
|
|
|
Mar 13 2012, 10:32 AM
Post
#6
|
|
|
Access Wiki and Forums Moderator Posts: 47,919 From: SoCal, USA |
Hi,
Sorry if I am being slow... like I said, I don't do Outlook VBA. But if you will bear with me, here's what I was thinking: 1. Create a New Rule in Outlook 2. Select "Check messages when they arrive" 3. Select "with specific words in the body" 4. Click on "specific words" and change it to "johnnydoe@yahoo.com" 5. Click Next and select "forward it to people or distribution" 6. Click on "people or distribution" select the email or contact name from your address book 7. Click Finish No VBA. Will that work? Just my 2 cents... (IMG:style_emoticons/default/2cents.gif) |
|
|
|
Mar 14 2012, 08:05 AM
Post
#7
|
|
|
UtterAccess Member Posts: 23 |
I wish a rule would work and it would be that easy.
The problem is the the email address will change every time. So I need something that will return an email address string found in the body of the email, create a new email with the found string as the TO: address. Thanks for your input though. |
|
|
|
Mar 14 2012, 09:42 AM
Post
#8
|
|
|
Access Wiki and Forums Moderator Posts: 47,919 From: SoCal, USA |
I wish a rule would work and it would be that easy. The problem is the the email address will change every time. Oh, I see... That wasn't clear in your earlier descriptions. QUOTE So I need something that will return an email address string found in the body of the email, create a new email with the found string as the TO: address. Will you have a "list" of email address to look for? Or else, how will the code know which email address to search for? Or, are you looking for a "pattern?" QUOTE Thanks for your input though. (IMG:style_emoticons/default/yw.gif) As I said though, I really don't use Outlook VBA so I hope somebody else could give you the answer you're looking for. Good luck! |
|
|
|
Mar 14 2012, 04:25 PM
Post
#9
|
|
|
UtterAccess Member Posts: 23 |
QUOTE Will you have a "list" of email address to look for? Or else, how will the code know which email address to search for? Or, are you looking for a "pattern?" To be honest, I am not quite sure. There should be only one email address located in the body. Below is a sample of the type of email that will come in: The application was completed with the below information: Last Name: Smith First Name: Joe Social Security #: Email Address: youremail@yourchoiceofdomain.com Address: 123 Your Address St City: City State: State Zip: 12345 Home Phone: 555-555-5555 Position Applied for: Assembler Department: N/A This is what I have gathered so far: CODE Public Sub ParseMail(Mail As Outlook.MailItem) Dim pos& Dim SLeft As String Dim SRight As String Dim SEmail As String Dim OMailItem As MailItem Dim MailBody As String MailBody = Mail.Body pos = InStr(MailBody, "@") If pos > 0 Then SLeft = Left(MailBody, pos) SRight = Mid(MailBody, pos) SEmail = SLeft + SRight Set OMailItem = Application.CreateItem(olMailItem) With OMailItem .To = SEmail .Subject = "Thank you for applying" .Display End With Set OMailItem = Nothing End If End Sub; This runs as a script which is connected to a rule. Two things are happening: 1) When an email comes in, it's starting two new reply messages. 2) It's putting the WHOLE body as the "TO" address. Any ideas?? This post has been edited by crb14: Mar 14 2012, 04:32 PM |
|
|
|
Mar 14 2012, 04:43 PM
Post
#10
|
|
|
Access Wiki and Forums Moderator Posts: 47,919 From: SoCal, USA |
Hi,
This runs as a script which is connected to a rule. Two things are happening: 1) When an email comes in, it's starting two new reply messages. 2) It's putting the WHOLE body as the "TO" address. Any ideas?? 1) Probably depends on what the rule is set to do. For example, if the rule is set to create an email and your script also creates an email, you will get two emails. 2) Pretend the body of the email is something like this: "My email address is theDBguy[at]gmail[dot]com" (used [at] in place of @ to avoid spambot harvesting), then this code: pos = InStr(MailBody, "@") will result in the variable pos containing the value 29. Which means this line: SLeft = Left(MailBody, pos) will result in the variable SLeft having the value of "My email address is theDBguy[at]." And this line: SRight = Mid(MailBody, pos) will result in the variable SRight having the value of "[at]gmail[dot]com." Which means, this line: SEmail = SLeft + SRight will actually have something like: "My email address is theDBguy[at][at]gmail[dot]com" Try changing your code to something like: SLeft = Mid(MailBody, InStrRev(Left(MailBody,pos-1), " ")) SLeft = Left(SLeft, InStr(SLeft, "@")-1) SRight = Mid(MailBody, pos) SRight = Left(SRight, InStr(SRight, " ")) (untested) Just my 2 cents... (IMG:style_emoticons/default/2cents.gif) |
|
|
|
Mar 14 2012, 04:55 PM
Post
#11
|
|
|
UtterAccess Member Posts: 23 |
Brillant!!
Still some issues: It is still opening two new messages. The rule is not set up to send an email, just the script. Also it is putting "mailto:" infront of the email address, which obviously the email won't send. I feel like I am getting so close I can taste it. Thanks for your help and quick response!! This post has been edited by crb14: Mar 14 2012, 04:55 PM |
|
|
|
Mar 14 2012, 05:16 PM
Post
#12
|
|
|
Access Wiki and Forums Moderator Posts: 47,919 From: SoCal, USA |
Hi,
(IMG:style_emoticons/default/yw.gif) Like I said earlier, Outlook VBA is not really my strong suit. I wish I could do some testing for you. Good luck! |
|
|
|
Mar 16 2012, 12:34 PM
Post
#13
|
|
|
UtterAccess Member Posts: 23 |
I finally got it to work.
No rule needed and I created a template that is sent, which eliminated two emails being opened. Weird, I know. Also created a macro signature so the security didn't have to be adjusted for the code to work. CODE Private WithEvents Items As Outlook.Items Private Sub Application_Startup() Dim Ns As Outlook.NameSpace Set Ns = Application.GetNamespace("MAPI") Set Items = Ns.GetDefaultFolder(olFolderInbox).Items End Sub Private Sub Items_ItemAdd(ByVal Item As Object) If TypeOf Item Is Outlook.MailItem Then ParseMail Item End If End Sub Public Sub ParseMail(Mail As Outlook.MailItem) 'Setting up variables Dim pos& Dim SLeft As String Dim SRight As String Dim SEmail As String Dim OMailItem As MailItem Dim MailBody As String If Mail.SenderEmailAddress = "website@yourdomain.com" Then 'Setting string to email body text MailBody = Mail.Body 'Getting position of "@" in email address in body of email pos = InStr(MailBody, "@") 'If there is an email address then If pos > 0 Then 'Finding the starting position for characters LEFT of "@" SLeft = Mid(MailBody, InStrRev(Left(MailBody, pos - 1), " ")) 'Returning the characters LEFT of "@" SLeft = Left(SLeft, InStr(SLeft, "@") - 1) 'Getting rid of mailto: at the beginning SLeft = Mid(SLeft, InStr(1, SLeft, ":") + 1) 'Finding the starting position for characters RIGHT of "@" SRight = Mid(MailBody, pos) 'Returning the characters RIGHT of "@" SRight = Left(SRight, InStr(SRight, " ")) 'Getting TO string SEmail = SLeft + SRight Set OMailItem = Application.CreateItemFromTemplate(Environ("APPDATA") & "\Microsoft\Templates\Confirmation Receipt of Job Application.oft") With OMailItem .SentOnBehalfOfName = "hrdept@yourdomain.com" .To = SEmail .Send End With Set OMailItem = Nothing End If End If End Sub Hope this can help someone else! Thanks |
|
|
|
Mar 16 2012, 12:47 PM
Post
#14
|
|
|
Access Wiki and Forums Moderator Posts: 47,919 From: SoCal, USA |
Hi,
Glad to hear you found a solution that works for you. Not sure if I'm reading that right but does the code only runs when Outlook is first opened? Or, will it search and forward emails as you get them in your Inbox after you have started Outlook and leave it open all day? Just curious... Thanks for sharing! (IMG:style_emoticons/default/thumbup.gif) |
|
|
|
Mar 16 2012, 01:30 PM
Post
#15
|
|
|
UtterAccess Member Posts: 23 |
It will check emails as you receive them throughout the day.
Thanks! |
|
|
|
Mar 16 2012, 01:33 PM
Post
#16
|
|
|
Access Wiki and Forums Moderator Posts: 47,919 From: SoCal, USA |
It will check emails as you receive them throughout the day. Thanks! Okay, thanks. That's good to know. Good luck! (IMG:style_emoticons/default/cheers.gif) |
|
|
|
![]() ![]() |
|
Go to Top · Lo-Fi Version | Time is now: 20th May 2013 - 06:47 AM |