UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Automate Replies In Outlook, Office 2007    
 
   
crb14
post 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!!
Go to the top of the page
 
+
theDBguy
post 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)
Go to the top of the page
 
+
crb14
post 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
Go to the top of the page
 
+
theDBguy
post 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)
Go to the top of the page
 
+
crb14
post 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.
Go to the top of the page
 
+
theDBguy
post 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)
Go to the top of the page
 
+
crb14
post 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.
Go to the top of the page
 
+
theDBguy
post Mar 14 2012, 09:42 AM
Post #8

Access Wiki and Forums Moderator
Posts: 47,919
From: SoCal, USA



QUOTE (crb14 @ Mar 14 2012, 06:05 AM) *
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!
Go to the top of the page
 
+
crb14
post 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
Go to the top of the page
 
+
theDBguy
post Mar 14 2012, 04:43 PM
Post #10

Access Wiki and Forums Moderator
Posts: 47,919
From: SoCal, USA



Hi,

QUOTE (crb14 @ Mar 14 2012, 02:25 PM) *
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)
Go to the top of the page
 
+
crb14
post 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
Go to the top of the page
 
+
theDBguy
post 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!
Go to the top of the page
 
+
crb14
post 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
Go to the top of the page
 
+
theDBguy
post 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)
Go to the top of the page
 
+
crb14
post Mar 16 2012, 01:30 PM
Post #15

UtterAccess Member
Posts: 23



It will check emails as you receive them throughout the day.

Thanks!
Go to the top of the page
 
+
theDBguy
post Mar 16 2012, 01:33 PM
Post #16

Access Wiki and Forums Moderator
Posts: 47,919
From: SoCal, USA



QUOTE (crb14 @ Mar 16 2012, 11:30 AM) *
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 the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Go to Top  ·  Lo-Fi Version Time is now: 20th May 2013 - 06:47 AM