Scanning mailboxes and generating email replies.

[aah-boy]

Hi all,

Thanks for the help last night, but now I have hit another wall.

My code should loop round each message in the inbox, give me the time
received on my spreadsheet, pause - so I can change the problem
resolution and then give me the option of sending it back to the user.

Problem: How can I pause it after each .display of the email message?
It generates 100's of outlook windows!

Sorry this is more of an Outlook Q. than Excel, but I am kicking it
all off from within Excel - honest.

Here is my code...

For Each olMail In Fldr.Items
If InStr(olMail.Body, "PM123456") > 0 Or _
InStr(olMail.Subject, "PM123456") > 0 Then
'ActiveSheet.Cells(i, 1).Value = olMail.ReceivedTime
With olMail
.Display
End With
End If
Next olMail

Btw. the commented ActiveSheet line fails with 1004 - application
defined or object defined error.

Many thanks,
Dave

 

[Dick Kusleika]

Dave

You can't really pause the macro for free-form user interaction like that.
Walk me through what you want to do - here's what I have so far.

You have 100 emails all dealing with the same problem. You have a
resolution and want to email to all these people what that resolution is.
So you loop through the emails and pick only those that have a certain
problem code in the subject. Now what do you want to do?

Do you want to read the message? Do you want to only reply to certain
messages and not all of them - and if so, how will you decide which?

If you just want to reply to all the messages, then your code might look
like this

Dim ReplyMsg as String
Dim ReplyMi as MailItem

ReplyMsg = "here's the resolution... blah, blah, blah"
i=1

For Each olMail In Fldr.Items
If InStr(olMail.Body, "PM123456") > 0 Or _
InStr(olMail.Subject, "PM123456") > 0 Then

ActiveSheet.Cells(i, 1).Value = olMail.ReceivedTime
i = i + 1
Set ReplyMi = olMail.Reply
With ReplyMi
.Body = ReplyMsg & vbCrLf & vbCrLf & olMail.Boy
.Send
End With
End If
Next olMail

Note that this doesn't display the original message, just replies to it and
adds text to the top. The problem with the ActiveSheet is probably that
i=0, so I set i=1 and incremented i for each MailItem that was found.

Based on the answers you provide to the above questions, this may need some
adjustment. For instance, if there are some emails to which you don't want
to reply.

 

[aah-boy]

Dave

Thanks for the reply Dick - spot on - thank you very much for taking
the time. You actually have a finer solution than mine, which was to
display each new email and paste the solution in. Rather lame now that
I consider what you have said.

The only problem I still may have is the 'sender' needs to be set -
currently everyone has to manually type 'the centre' into it - or
emails are sent from personal accounts.

I guess changing the 'sender' is their way of stopping our customers
emailing us directly and by-passing the email filter system they use.

Other than this, I agree, your solution seems damn good.

Based on the answers you provide to the above questions, this may need some
adjustment. For instance, if there are some emails to which you don't want
to reply.

No, I need to reply to every one of them that contains the problem no.

Again, many thanks.

Dave

 

[Dick Kusleika]

Dave

The only problem I still may have is the 'sender' needs to be set -
currently everyone has to manually type 'the centre' into it - or
emails are sent from personal accounts.

No small problem, unfortunately. Check out the SentOnBehalfOf property in
help and see if that does what you want - or even close. If not, you will
need to use CDO (another object model) to change the sender's name. See
here

http://www.cdolive.com/cdo5.htm#MessageOriginator

and here's some general information on using CDO

http://www.rondebruin.nl/cdo.htm

If you end up using CDO, you will probably have to use a combination of
Outlook and CDO object models. Outlook to retrieve the email and CDO to
send it.

I hope that gets you started. I will be away from the groups for a couple
of days, so if you ask a question and don't get a response, feel free to
send me a personal email and I will get back to you next week.

 

[aah-boy]

Dave

Dick

Thanks again for the wonderful help - you should wear a cape and a
mask !!

No small problem, unfortunately. Check out the SentOnBehalfOf property in
help and see if that does what you want - or even close. If not, you will
need to use CDO (another object model) to change the sender's name. See
here

Yes, that (onbehalfof) seems to be putting the sender in there -
wacko!!

I'll try it out tomorrow at work. Should save me endless keystrokes if
it works.

Work smarter not harder is my motto.

http://www.cdolive.com/cdo5.htm#MessageOriginator

and here's some general information on using CDO

http://www.rondebruin.nl/cdo.htm

If you end up using CDO, you will probably have to use a combination of
Outlook and CDO object models. Outlook to retrieve the email and CDO to
send it.

I hope that gets you started. I will be away from the groups for a couple
of days, so if you ask a question and don't get a response, feel free to
send me a personal email and I will get back to you next week.

Enjoy your break,
Dave