How to send a same reply mail (template saved in outlook) to the mail sender (to, cc)?

Question

I am receiving continues emails from customer (different customers) to update their asset details in database.. once process done .. I have to reply (including cc) from their mail telling like "asset details successfully stored in Database" (am using template) using VBA.

Option Explicit


Public Sub ReplyToAll()

Dim oExp As Outlook.Explorer

'for selected mails in outlook

Dim oSM As mailItem

Dim oNM As mailItem

    On Error GoTo Err
    
    Set oExp = Outlook.Application.ActiveExplorer
    
    'Check if something is selected
    If oExp.Selection.Count > 0 Then
        
        'Get the first item selected

        Set oSM = ActiveExplorer.Selection.Item(1)
        
            'Create a Reply template

            Set oNM = oSM.ReplyAll
            
            With oNM
                
                'Change the subject
                .Subject = "RE: " & oSM.Subject
                
                'Change the body
                .Body = .Body & Chr(13) & Chr(13)
                
            'Display the new mail before sending it
                .Display
            
            End With
            
    End If
    
    Exit Sub
    
Err:
    
    MsgBox Err.Description, vbCritical
    
End Sub

section 3

Sub ReplyAll()
    Dim objOutlookObject As mailItem
    
For Each objOutlookObject In GetCurrentOutlookItems

    With objOutlookObject
    .ReplyAll.Display
'prob area code does not include the template saved in the location c ..throws some error

    .createitemtemplate("c:\car.jtm")
   End With
Next
End Sub
 
Function GetCurrentOutlookItems() As Collection
    Dim objApp As Outlook.Application
    Dim objItem As Object
    Dim colItems As New Collection
        
    Set objApp = CreateObject("Outlook.Application")
    On Error Resume Next
    Select Case TypeName(objApp.ActiveWindow)
        Case "Explorer"
            For Each objItem In objApp.ActiveExplorer.Selection
                colItems.Add objItem
            Next
        Case "Inspector"
            colItems.Add objApp.ActiveInspector.CurrentItem
        Case Else
            ' anything else will result in an error, which is
            ' why we have the error handler above
    End Select
    
    Set objApp = Nothing
    Set GetCurrentOutlookItems = colItems
End Function

Answer 1

I am sorry my comment was so curt; 500 characters does not leave much room for a full answer.

Your question is very unclear so it is likely to be closed. Certainly, I do not see how anyone could answer it. That is why it is important that you try to solve your own problem and return as necessary with specific questions.

Below I provide links to recent posts that I believe will help you get started. As I said in my comment, look through recent posts. Use the search facility. There are some very good answers here if you look for them.

The first two posts are tutorials written by me. The early steps are the same but, because the questions were not quite the same, later steps are different. Look at both and pick out the bits relevant to you. The others posts all contain information you may find helpful.

How to import the outlook mail data to excel

update excel sheet based on outlook mail

could anyone guide me in creating an outlook macro that does the following

send an email from excel 2007 vba using an outlook template set variables

using visual basic to access subfolder in inbox

vba outlook event moving email

New section in response to new information from questioner

Except for minor modifications, the code in your question was taken from the Microsoft Help file for NewMailEx Event. This code will only work if you have the correct type of installation and if you place it in the correct place:

• "The NewMailEx event will only fire for mailboxes in Microsoft Outlook that provide notification for received message such as Microsoft Exchange Server. Also, the event will fire only if Outlook is running. In other words, it will not fire for the new items that are received in the Inbox when Outlook was not open. Developers who want to access these items for customers running Outlook on an Exchange server e-mail account need to implement their code on the server. However, the NewMailEx event will fire against Cached Exchange Mode in all settings: Download Full Items, Download Headers, and Download Headers and then Full Items."

Do you have the correct type of installation? Can you place your code on the server? Even if this is the correct approach for the final version of your macro, I do not believe it is the correct approach while you are learning VBA and Outlook.

You need two things:

1. a detailed specification of the macro you wish to write and
2. more understanding of VBA and Outlook.

I doubt you can create the detailed specification yet because you do not know enough about VBA and Outlook. But we can list things you will need to know:

• How do you write to your database from Outlook?
• How do you identify the mail items you wish to record? In your example, you are checking for a subject of "Hello" and replying "Hi". This is fine for a first experiment but you need to identify the real method. Is it a new sender? Is there specific information in the body of the message? Does a human have to identify such mail items?
• In your example, you have a folder "Personal" under "Inbox". Many people seem to have this type of folder structure and Microsoft examples tend to use folders like this. I do not. I have a folder called "!Home". Under this I have folders for "Insurance", "Shopping", "Money". Under these I have folders for my different suppliers. Once I have dealt with a message, I move it to appropriate folder. Replies go to the same folder. This is my system and it works for me. What is your system going to be? Will, for example, there be a single folder for all customers or one per customer?

The above is a starter list of questions for your specification but it is also a starter list of things you need to know.

Perhaps you have a boss who wants you to stop wasting time and start writing the macro but you do not know enough yet to plan the final macro.

Start with my tutorials. The first three steps are about the folder structure. These steps are essential if you have the kind of complex folder structures I have. Next I go through a folder displaying selected information from each mail item. I have steps in which I write message bodies to disc. I suggest you go through both tutorials and try my code. Not all of it will be immediately useful but it is all good background information.

What is your database? Is it Access or Excel? There is some help in my tutorials and in the other links above with writing to Excel which you could adapt for Access.

I think the above is enough for now. Take it slowly and it will start to make sense. I still remember the first time I tried to write an Outlook macro so I understand your confusion. I promise that it will become clear. Best of luck.

New section in response to the following comment:

• "hello i have tried ..Got what i want....Removed my previous code..and tried replaced the new code .. Now little help needed from you ....is there any way to use same format like when we click the replyall button in outlook .. my code working fine ..prob is format of the mail is differ .."

Problem 1

.Body = .Body & Chr(13) & Chr(13)

You are using the text body. I think you want the HTML body. Try:

.HTMLBody = .HTMLBody & Chr(13) & Chr(13)

Problem 2

You cannot add to the HTML body in this way. The HTML body will be:

<!doctype ...><html><head> ... </head><body> ... </body></html>

You must add your text to the beginning of the body; that is, just after <body>. If you just add your text, you will be accepting whatever style, margins and colours the sender has used. The following code adds some text that looks the same in every email I have tried it with. My text is within a table with a single cell. The table covers the full width of the page. The text is blue on a white background.

Dim InsertStg As String
Dim Inx As Long
Dim Pos As Long

    'Change the body step 1: Create the string to be inserted
    InsertStg = "<table border=0 width=""100%"" style=""Color: #0000FF""" & _
                " bgColor=#FFFFFF><tr><td><p>"
    For Inx = 1 To 10
      InsertStg = InsertStg & "Sentence " & Inx & " of first paragraph. "
    Next
    InsertStg = InsertStg & "</p><p>"
    For Inx = 1 To 10
      InsertStg = InsertStg & "Sentence " & Inx & " of second paragraph. "
    Next

    ' The following adds a signature at the bottom of the message.
    ' "font-family" gives a list of fonts to be tried.  If these are
    ' missing from your computer, use the names of fonts you do have.
    ' "serif" means that if none of the fonts exist any serif font
    ' that exists is to be used.
    InsertStg = InsertStg & "</p><p style = ""font-family: Mistral, " & _
                "Vivaldi, serif; font-size: 14px; color: " & _
                "rgb(127,0,127)"">John Smith<br>5 Acacia Avenue<br>"

    InsertStg = InsertStg & "</p></td></tr></table>"
    'Change the body step 2: Find insertion position just after <Body> element
    Pos = InStr(1, LCase(.HTMLBody), "<body")
    If Pos = 0 Then
      Call MsgBox("<Body> element not found in HTML body", vbCritical)
      Exit Sub
    End If
    Pos = InStr(Pos, .HTMLBody, ">")
    If Pos = 0 Then
      Call MsgBox("Terminating > for <Body> element not found in HTML body", vbCritical)
      Exit Sub
    End If
    'Change the body step 3: Insert my text into body
    .HTMLBody = Mid(.HTMLBody, 1, Pos) & InsertStg & Mid(.HTMLBody, Pos + 1)