Access VBA ReplyAll to Outlook Email

[ClearlyDarkly]

Good Morning,

I have been pulling out my hair for the past few days trying to figure this out...

My Access database is a "Logistics TSM" we use for one of our customers and i have been given the task to add automation functionality as a cost saving which needs to handle email orders, absorb the information into the database and then Reply all to the Sender and CC confirmation that their order has been taken care of and give them a reference.

Here are some Dims.

 Dim olApp As Outlook.Application, objNS As NameSpace
Dim CorporateInbox As Folder, MailItems As Items, fldr As Folder
Dim Catagories As String, MyFile As String
Dim InportError As Integer, Completed As Integer, UnderReview As Integer
Dim MAPI As NameSpace
Dim Source As Folder
Dim MailCounter As Integer, MailCounterCount As Integer
Dim doClip As MSForms.DataObject
Dim MSExcel As Object ' Excel.Application
Dim MailNumber As Long

Getting Email Stuff, (double debug.print prevents the infinite do loop to not crash the system

Set MAPI = GetNamespace("MAPI")
Set Source = MAPI.Folders("CT Sainsburys (DHL GB)").Folders("Inbox")

MailCounterCount = Source.Items.Count
For MailCounter = Source.Items.Count To 1 Step -1

    ExcelTitle = ""
    Set Mail = Source.Items(MailCounter)
    Catagories = Source.Items(MailCounter).Categories
    RecievedDate = Source.Items(MailCounter).ReceivedTime

    Debug.Print Mail.Subject
    ExcelTitle = Mail.Subject & " - " & Mail.ReceivedTime & ".xlsx"
    ExcelTitle = Replace(Replace(Replace(Replace(ExcelTitle, "]", "."), "[", "."), "/", "."), ":", "")
    Debug.Print ExcelTitle

Then a lot of code for copying and pasting the HTMLBody into an Excel Spreadsheet, and importing via recordsets.

the some code for saving any attachments and importing that info.

Changing the email subject and catagory

                                                                                                                        Source.Items(MailCounter).Subject = Mail.Subject & " - Reference " & Reference
                                                                                                                        Source.Items(MailCounter).Categories = "Import Success"
                                                                                                                        Source.Items(MailCounter).Save

But the issue i have is how do i then do a replyall with my custom body.

Any help/guidence would be appreciated

 

[ClearlyDarkly]

UPDATE!

I have Figured out how to send an email as reply all... which is below

If Now() > #12:00:00 AM# Then

If Now() < #12:00:00 PM# Then

   TOD = "Morning"
                                                                                                                       End If

If Now() > #12:00:00 PM# Then
 
   TOD = "Afternoon"
 
End If
          
Reference = "BLERGH"

Set MsgReply = Source.Items(MailCounter).ReplyAll

With MsgReply
.HTMLBody = "<FONT color=#000000 face=Calibri size=3>" & _
"<p>Good " & TOD & "</p><p><p/p> " & _
 "<p>Thank you for your email. Your request has been received. Your Unique Reference Number is '" & Reference & "'. Please use this with future queries regarding this order</p><p><p/p> " & _
"<p>This is an Automatic Email</p><p><p/p> " & _
"Company Name" & _
"<BR><\BR>" & _
"ADDRESS1" & _
"<BR><\BR>" & _
 "STREET" & _
 "<BR><\BR>" & _
"TOWN" & _
"<BR><\BR>" & _
"COUNTY" & _                                                                                                                        "<BR><\BR>" & _
"POSTCODE" & _
"</html>"
.Send

End With

TOD = ""

However the Issue i am now having now is including the initial email in the ReplyAll...