VBA To Reply All Email

[IgnoranceIsBliss]

If I change as suggested I now get a error in the line
Next olMail

The error is Type Mismatch

 

[June7]

I ran code and doesn't error but doesn't find email either. Because the TO value is Laura which is not the email address I searched for. Search for Laura - without apostrophes - worked. So using aliases complicates.

Object and MailItem both worked.

Email not found message is in wrong place. Code terminates after first message encountered that doesn't match. What if matching email is 10 messages down?

 

[GC2010]

Hmmm....peculiar...Inside your For Each block add these Debug Statements (Thanks @June7 for reminding me to always Debug!)

    Debug.Print olMail.To
    Debug.Print olMail.SentOn

Then in manually scan your Sent Email for the last email that is Printed in Access. Once you find that what is the email directly above it?

(Unless you specify otherwise, this will scan in Ascending order, or from oldest to newest)

 

[June7]

Can have a single Debug line:

Debug.Print "TO: " & olMail.To & " ON: " & olMail.SentOn

Need to remove the Else branch.

 

[IgnoranceIsBliss]

The message above the last one printed is a meeting invite for Skype for Business.

Is there a way to search only emails?

 

[GC2010]

The message above the last one printed is a meeting invite for Skype for Business.

Is there a way to search only emails?

We are above my skill set at this point.

Maybe a more advanced Access VBA user can step in here

 

[GC2010]

Can have a single Debug line:

Debug.Print "TO: " & olMail.To & " ON: " & olMail.SentOn

Need to remove the Else branch.

Love it!!! I never thought of concatenation this to a one liner!!

 

[GC2010]

We are above my skill set at this point.

Maybe a more advanced Access VBA user can step in here

I may have thrown in the towel a little early here...

If you run this syntax you should get MailItem for any email message and (using the Skype meeting request example above) get MeetingItem for any Skype Meeting

For Each msg In Fldr.Items
	Debug.Print TypeName(msg)
Next msg

In knowing this, we can now filter our VBA search on ONLY MailItem, thus removing the error

Function Test()

Dim searchEmail As String: searchEmail = "'abc123@abc123.com'"
Dim olApp As Outlook.Application
Dim olNs As NameSpace
Dim Fldr As MAPIFolder
Dim olReply As Outlook.MailItem
Dim msg As Object
Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(olFolderSentMail)

For Each msg In Fldr.Items
	If TypeName(msg) = "MailItem" Then
		If msg.To = searchEmail Then
			Debug.Print "To: " & msg.To & " On: " & msg.SentOn
			Set olReply = msg.ReplyAll
			With olReply
				'now you have control of the email you can add any text you want
				.BodyFormat = olFormatHTML
				.Display
			End With
		End If
    End If
Next msg

End Function

Now I'll also warn again, that I have not tested this when multiple emails exist in the Sent Items to one sender. Personally I would modify this to add in msg.SentOn parameters to narrow down your search, and depending on mailbox size reduce scan time.

 

[sxschech]

Been putting something together based on my understanding of the request. Seems to be working. To speed up the process, specify a search after date (can be hard coded) or put in a table so that it doesn't need to be changed often, then key in the last name (or name) of the person, it uses a wildcard to search allowing for using partials. It then locates and displays the most recent date that an email was sent, regardless of if they were the only person in the To or part of a list of recipients. After that creates a reply all to that message. Am I on track? I may not have much time to work on it tomorrow. Once I have tested it a bit more, I can post to see if it meets your needs.

 

[IgnoranceIsBliss]

Hi - post 28 - is functioning for me, I just have one mishap I am having with he code.

When I open the email, I want to ADD new text to the email chain. When I use the code in this post it ONLY adds the 'new' text that I code in.

With olReply
   .BodyFormat = olFormatHTML
   .HTMLBody = emailBody
  .Save
  .Close olSave
End With

How can I add text to my response and retain the original email thread?

 

[sxschech]

This is a bit more code and process than the other posts provided. From what I could tell, the other code doesn't actually find the latest message, just finds a message.

-----
Since I copied and slightly modified code from my other dbs, I may have missed something. I didn't receive errors, but it possible there may be errors if run on another system. I left code in that includes a signature. This will need to be edited or deleted. SEE NOTE ABOUT SIGNATURE in MODULE1 below before trying to run this to avoid potential error message.
----

Here is a sample db to obtain the latest email sent to someone and includes the message thread. Since not sure how you are selecting the name, I am using simple text boxes. When the form opens there 3 boxes and 3 buttons.



Search After (Default is 30 days before today, can enter any date)
This is used to create a list of emails from the sent folder that were sent after that date. This only needs to be run as needed when there have been more emails added. Does not need to be run every time under the assumption that the list to search is not being updated constantly. Saves time by not having to update often.

Sent To, provide the name to search for. Can be a partial name or the whole name. This will search for any message the name appears in whether they are the only to or part of a list of recipients.

Date Sent: displays the date of the most recent email that contains the sent to name. (This date is disabled so can't be edited on the form)

Create Reply retrieves the email sent to that person on that date/time and opens it up in Reply All, so if the message was sent to more people, they will also be included. Message includes whatever text was in the original message, just as is what takes place when clicking Reply All. (Actually is searching for the date and subject rather than date and person)

NOTES
--------
FORM1:
I commented out the message text as not sure if you need that for your purpose since you can type a message directly in the email. If you want to use the message via code, simply uncomment it and edit the text to your message.

MODULE1:
If you use a signature, then you will need to edit the code to provide the name and location of the signature file. (Search for SigString in Module1)
If you don't need signature, can comment out the code in the appropriate places.

The Outlook_ReplyAll has a few optional parameters, for processing emails.