Excel VBA, how to Reply to a specific email message

Question

I receive a mail every wednesday from a specific sender. The subject of this email sometimes changes

Example #1 of subject "Exposure statement - COB 20150217"

Example #2 of subject "Margin Notice COB 2015-Feb-10"

The date the sender append is the day before the day I receive the mail.

I have the following code wich might search for that email and then reply to it with a custom body text but I can't manage to let the code to find that specific message with that date in the subject.

Is there a way to search by other parameters than the subject?

Sub ReplyMail_No_Movements()

Dim olApp As Outlook.Application
Dim olNs As Namespace
Dim Fldr As MAPIFolder
Dim olMail As Variant
Dim SigString As String
Dim Signature As String
Dim i As Integer

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(olFolderInbox)
i = 1

SigString = Environ("appdata") & _
                "\Microsoft\Signatures\MCC.txt"

    If Dir(SigString) <> "" Then
        Signature = GetBoiler(SigString)
    Else
        Signature = ""
    End If

    On Error Resume Next

For Each olMail In Fldr.Items
If InStr(olMail.Subject, "Exposure Statement - COB date") <> 0 Then 'where date is a date that changes every wednesday
With olMail.Reply
        .to = "email1@domain.com;email2@domain.com"
        .CC = "email3@domain.com;email4@domain.com"
        .Body = "Dear All," & Chr(10) & _
        Chr(10) & "we agree with your portfolio here attached and according to it we see no move for today." & _
        Chr(10) & "        Best Regards." & _
        Chr(10) & _
        Chr(10) & Signature
        .Display
    End With
i = i + 1
End If
Next olMail
End Sub

Edit: I changed this code bit from

If InStr(olMail.Subject, "Exposure Statement - COB date") <> 0 Then

to

If olMail.SenderEmailAddress = "email1@gdomain.com" And olMail.ReceivedTime = Now() Then

But it doesn't work...

This is the only search combo (SenderEmailAddressthat and ReceivedTime) that let me find the exact message...

Make sure that you did all the steps described in the [How to automate Outlook from another program](https://support.microsoft.com/kb/201096?wa=wsignin1.0) article. — Eugene Astafiev, Feb 19 '15 at 16:55
you can access and filter any properties of MaiItem class (here is full list): https://msdn.microsoft.com/en-us/library/microsoft.office.interop.outlook.mailitem_properties.aspx but you didn't specify what exactly do you want to filter. Btw.: your recent edit completely changed the sense of the question, so the answers do not make any sense. You should have pasted the new code below the original question. — BrakNicku, Feb 20 '15 at 11:54
That's why I asked where to put the updated code... I did a mess... :( — aurezio, Feb 20 '15 at 12:28
The second part `olMail.ReceivedTime = Now() ` because you're trying to filter the messages that are received exactly at the time you run the macro. If you want the message from the current day then `olMail.ReceivedTime>Date()` should work — BrakNicku, Feb 20 '15 at 12:45
Yes it worked, thanks. And what if the sender send me another mail after the first one in that day? :) — aurezio, Feb 20 '15 at 13:42
You need to mark somehow the mail you already processed. Easiest solution would be to use Unread property. Filter only "Unread = True" and set it to false after creating reply. PS. Already a lot of questions not related to the original question, so please create another question if you have any further problems, this way others could also see it/help — BrakNicku, Feb 20 '15 at 13:51
Ok, sorry for that, I also need to learn how to behave on this forum :) — aurezio, Feb 20 '15 at 14:09

BrakNicku · Accepted Answer · 2015-02-19T16:56:00.343

2

You shoud use: Tools->References. Find Microsoft Outlook 15.0 Object Library, check it and close the window.

edited Feb 19 '15 at 16:56

answered Feb 19 '15 at 16:50

BrakNicku

5,935
3
24
38

Wow, thanks... real noobie here. It works. I only have to add the code for replying to the email with a custom text now... Any idea? – aurezio Feb 19 '15 at 17:08
olMail.ReplyAll returns a MailItem object, You can then set various properties of the the reply and send it. Outlook will issue security warnings on some actions if you do not configure it to allow programmatic access. – BrakNicku Feb 19 '15 at 17:21
I managed to get the proper code for replying with a custom body text, custom recipients and custom subject but it misses the original attachment. Is there a code to maintain the original attached file to the reply? – aurezio Feb 19 '15 at 17:25
You need to iterate all attachments from original mail, save it, and add to the reply mail. Example code can be found here: http://www.slipstick.com/outlook/email/reply-replyall-attachments/ – BrakNicku Feb 19 '15 at 17:29
Thanks again, will take a look tomorrow and let you know. Grazie mille! – aurezio Feb 19 '15 at 17:33
Still have to look into the attachment thing 'cause I first have to set the search parameters for the mail. The subject search is not enough for my task. Can I search by other parameters? Like sender o/and date. Every wednesday I receive a mail from a certain sender but the subject is not always the same because the sender add the date of that wednesday to it. – aurezio Feb 20 '15 at 09:12
I would like to add the updated code but I don't know if I have to add it here in the comment or in the original post... – aurezio Feb 20 '15 at 09:26
The long code in comment is not a good idea, so you can edit the question and post updated code, or even better - create a new question and describe all the problems you have with it, it looks unrelated to the original question. – BrakNicku Feb 20 '15 at 10:29

score 1 · Answer 2 · answered Feb 20 '15 at 00:19

1

Or just use late binding

Const olFolderInbox = 6
Sub Test()

Dim olApp As Object
Dim olNs As Object
Dim Fldr As Object
Dim olMail
Dim i As Long

Set olApp = CreateObject("Outlook.Application")
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(olFolderInbox)
i = 1

For Each olMail In Fldr.Items
    If InStr(olMail.Subject, "email message object text") <> 0 Then
    olMail.Display
    olMail.ReplyAll
i = i + 1
End If
Next olMail
End Sub

answered Feb 20 '15 at 00:19

brettdj

54,857
16
114
177

Hello, thanks for your reply. I fixed the problem with the previous answer but today I had to convert the early binding to late binding because of compatibility issues between different computers that must run the code. So I found your answer very useful. Just a question. Could you explain me the meaning of `Const olFolderInbox = 6`? Thanks! – aurezio May 20 '15 at 09:56

Excel VBA, how to Reply to a specific email message

2 Answers2

Linked