IgnoranceIsBliss
Registered User.
- Local time
- Today, 09:34
- Joined
- Jun 13, 2019
- Messages
- 35
If I change as suggested I now get a error in the line
Next olMail
The error is Type Mismatch
Next olMail
The error is Type Mismatch
VBA To Reply All Email (1 Viewer)
- Thread starter IgnoranceIsBliss
- Start date
June7
AWF VIP
- Local time
- Today, 08:34
- Joined
- Mar 9, 2014
- Messages
- 5,463
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?
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?
Last edited:
Hmmm....peculiar...Inside your For Each block add these Debug Statements (Thanks @June7 for reminding me to always Debug!)
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)
Code:
Debug.Print olMail.To
Debug.Print olMail.SentOnThen 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)
IgnoranceIsBliss
Registered User.
- Local time
- Today, 09:34
- Joined
- Jun 13, 2019
- Messages
- 35
The message above the last one printed is a meeting invite for Skype for Business.
Is there a way to search only emails?
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
Love it!!! I never thought of concatenation this to a one liner!!
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
Code:
For Each msg In Fldr.Items
Debug.Print TypeName(msg)
Next msgIn knowing this, we can now filter our VBA search on ONLY MailItem, thus removing the error
Code:
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 FunctionNow 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.
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
Registered User.
- Local time
- Today, 09:34
- Joined
- Jun 13, 2019
- Messages
- 35
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.
How can I add text to my response and retain the original email thread?
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.
Code:
With olReply
.BodyFormat = olFormatHTML
.HTMLBody = emailBody
.Save
.Close olSave
End WithHow can I add text to my response and retain the original email thread?
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.
-----
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.
