Excel VBA, how to Reply to a specific email message
Solution 1
You shoud use: Tools->References. Find Microsoft Outlook 15.0 Object Library
, check it and close the window.
Solution 2
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
aurezio
Updated on June 04, 2022Comments
-
aurezio almost 2 years
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 = "[email protected];[email protected]" .CC = "[email protected];[email protected]" .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 = "[email protected]" 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...