VBA To Reply All Email

[IgnoranceIsBliss]

I have seen lots of threads on how to create an email with access vba, but I want to pass an email address to a 'search' function and scan my "Sent" mail in outlook for the most recent email to the email address.

Once found, I want to "Reply All" the email (so that it sends to the original recipient, not back to myself) and save it as a draft. Is this achievable with access vba 2016?

 

[Ranman256]

Sounds like it would be easier to open the email,then click Reply All.

 

[IgnoranceIsBliss]

I was assuming as much, but def not the answer I was hoping for.

I’m trying to automate a process that someone wastes hours on daily.

 

[jdraw]

Have you considered creating some procedures using vba in Outlook. I haven't used Outlook for email for years, but I did a few routines years back.
-Backup file attachments to file system
-Review emails and keep latest with same title

Outlook has its own object model that differs from Access/database.

I agree that opening and Reply All seems a sound approach.
Have you really quantified this

I’m trying to automate a process that someone wastes hours on daily.

Good luck

 

[IgnoranceIsBliss]

I’ll look into the outlook model and see if cvs can do it.

I was wanting this to take place from a button press event on an access form, but I’ll take almost any option!

 

[sxschech]

scan my "Sent" mail in outlook for the most recent email to the email address.

Does this mean if email address is name@email.com, the code should simply find the most recent email containing that address or does it need to be combined with a particular subject.

Email 1 To:xyz, abc, name, dl1 Subject:This is an early message Sent:20190105
Email 2 To:nbc, name, cbs Subject:This is a different message Sent:20190309
Email 3 To:nbc, name, dl1 Subject:Re: This is an early message Sent:20190315
Email 4 To:jjk, name, utw Subject:This is a new message Sent:20190428
Email 5 To:name, utw Subject:This is the newest message Sent:20190702


So would we pick email 5 the newest overall, or 3 which is a reply to message 1? If a particular subject, would we need to consider that the text of subject could be altered beyond the standard Forward, Reply?
This is an early message --> This was from an early message (March 2019)

 

[IgnoranceIsBliss]

Only requirement is to the specified email address.

If The email address was emailed 20 times and three times yesterday, then grab the latest time stamp so it’s the most recent email to the specified email address


Does that clarify?

 

[IgnoranceIsBliss]

I feel like I’m close, but I keep getting not found even tho I sent an email to this address today. What’s up?

Dim olFldr As Outlook.Folder
Dim olNs As Outlook.NameSpace

Set olNs = olApp.GetBamespace(“MAPI”)
Set olFldr = olNs.Folders(validemailtosearch).Folders(“Sent Items”)
For Each Outlook.MailItem in olFldr.Items
If Outlook.MailItem.To = ‘abc@cyz.com’ Then
Debug.Print Outlook.MailItem.Subject
Debug.Print Outlook.MailItem.Subject
Else
Debug.Print “not found”
End If
Next Outlook.MailItem

 

[Gasman]

Not sure if the site has swapped quote characters, but they are not the correct ones.
Regardless, walk through the code line by line and inspect the variables/properties.

 

[Deleted member 147267]

Why don't you have a table for dated email transactions as part of the original sending process? Then you could know the last send date for any recipient, the # of times, who was cc'd, attachment or no, etc. etc. and keep it all in Access.

 

[IgnoranceIsBliss]

Why don't you have a table for dated email transactions as part of the original sending process? Then you could know the last send date for any recipient, the # of times, who was cc'd, attachment or no, etc. etc. and keep it all in Access.

Knowing when it was sent isn’t as big of a deal. What my end game here is finding the last sent email and “reply all” and add text to that message from access vba. Now manually inputting this into an access table could help narrow down the specific date to scan sent mail for.

 

[IgnoranceIsBliss]

Not sure if the site has swapped quote characters, but they are not the correct ones.
Regardless, walk through the code line by line and inspect the variables/properties.

The site swapped out the quotes. I have stepped through the code and the issue I found is the .To is giving (forgive my ignorance and lack of terminology here) the name and not email.

For example, if I sent an email to Tom.jones@test123.com what the code shows is ‘Tom Jones’ not the actual email address

Is the .To the right field to be checking?

 

[Deleted member 147267]

Knowing when it was sent isn’t as big of a deal. What my end game here is finding the last sent email

These are really the same thing. Having the date allows you to find the last - based on date.

Is the .To the right field to be checking?

If automating Outlook from Access, I have no idea but others may. If keeping it all in Access as I suggested, then should be simpler - you get the email address from the table and it matters not how Outlook resolves it based on an address book.

 

[IgnoranceIsBliss]

These are really the same thing. Having the date allows you to find the last - based on date.
If automating Outlook from Access, I have no idea but others may. If keeping it all in Access as I suggested, then should be simpler - you get the email address from the table and it matters not how Outlook resolves it based on an address book.

Saving the data in access would only give me the email address and the date of last email sent...I'd still need to access the Outlook object to "Reply All" to the last email tho...

 

[Deleted member 147267]

Sorry, still don't see a problem.

Then you could know the last send date for any recipient, the # of times, who was cc'd, attachment or no, etc. etc.

etc. etc. includes every aspect of the prior email(s) that you need to see, regardless if that is To, CC, or BCC or whatever.
Maybe I'm missing your point, but if you check the email for these properties to find out what their values are, I see no reason why these values can't be in a table per message.

 

[IgnoranceIsBliss]

I am needing to reply all and keep the previous correspondence showing in the email thread.

Are you suggesting that every time an email is sent to manually add date/time email body, email subject etc to a local table?

 

[Deleted member 147267]

I am needing to reply all and keep the previous correspondence showing in the email thread.

Well, that seems like a new requirement to me, unless I missed where you said that already. I based my responses on what appeared to be somewhat different goals:

scan my "Sent" mail in outlook for the most recent email to the email address

grab the latest time stamp so it’s the most recent email to the specified email address

What my end game here is finding the last sent email and “reply all” and add text to that message from access vba.

I guess I took "that message" to mean in the new, next one, not add to the body of the prior email message. Not sure I would attempt to piece together email body contents of several messages into a table field. On the other hand, it's not hard for a person to intentionally or accidentally delete messages from an account, which would totally throw off which was the "last" that you're going to end up with.

So if I knew what you knew about your process, I might say yeah, put the body text in a memo field; either appended or in a related table - or not. I'm at a disadvantage here.

 

[GC2010]

I'm still learning VBA but I frankenstiened some code together a few months back to do a similar task. Maybe this will point you in the right direction.

What this does is it scans the sent mail folder of the email account and opens the message. You will have to modify to fit your criteria, as in my instance we will ONLY have 1 email per email address in the sent folder at a time. (The code may open 100 emails if you have emailed the search address that many times).

And I'm sure this code can use some clean-up and optimization, like I said, I'm still learning but have had some stellar help from people here!

Function OpenEmailToSpecifiedEmailAddress()

Dim searchEmail As String: searchEmail = "'abc123@abc123.com'"
Dim olApp As Outlook.Application
Dim olNs As NameSpace
Dim Fldr As MAPIFolder
Dim olMail As Object
Dim olReply As Outlook.MailItem

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(olFolderSentMail)

For Each olMail In Fldr.Items
    If olMail.To = searchEmail Then
            Set olReply = olMail.ReplyAll
        With olReply
			'now you have control of the email you can add any text you want
            .BodyFormat = olFormatHTML
            .Display
        End With
    Else
        Debug.Print "Email Not Found"
    End If
Next olMail

End Function

 

[IgnoranceIsBliss]

I'm still learning VBA but I frankenstiened some code together a few months back to do a similar task. Maybe this will point you in the right direction.

What this does is it scans the sent mail folder of the email account and opens the message. You will have to modify to fit your criteria, as in my instance we will ONLY have 1 email per email address in the sent folder at a time. (The code may open 100 emails if you have emailed the search address that many times).

And I'm sure this code can use some clean-up and optimization, like I said, I'm still learning but have had some stellar help from people here!

Function OpenEmailToSpecifiedEmailAddress()

Dim searchEmail As String: searchEmail = "'abc123@abc123.com'"
Dim olApp As Outlook.Application
Dim olNs As NameSpace
Dim Fldr As MAPIFolder
Dim olMail As Object
Dim olReply As Outlook.MailItem

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(olFolderSentMail)

For Each olMail In Fldr.Items
    If olMail.To = searchEmail Then
            Set olReply = olMail.ReplyAll
        With olReply
			'now you have control of the email you can add any text you want
            .BodyFormat = olFormatHTML
            .Display
        End With
    Else
        Debug.Print "Email Not Found"
    End If
Next olMail

End Function

Hi - I tried testing your code (no alterations) but I am getting an error of
Run-time error ‘438’:
Object doesn’t support this property or method

And this is the trouble line

If olMail.To = searchEmail Then

 

[GC2010]

The error is telling you (and someone please correct me if I speak incorrectly) that
An object is a class instance. A class instance has properties that are defined in the class type definition.

I’m thinking access doesn’t like us declaring olMail as object do let’s change that line to

Dim olMail As Outlook.MailItem

Also, wouldn’t hurt for you to add in an error handler to the code so you can dea with any issues that may arise from the code.