Link to home
Start Free TrialLog in
Avatar of rdy123 rdy
rdy123 rdy

asked on

vba in excel to send reply for sent email including original message

hi,

i am trying to write a macro in excel. the situation is i want to send a follow up mail which is already sent before,the mail is already in my sent items,i need to add up including the first original mail. Is this possible with a macro or VBA in excel? any help is thank full.
thanks in advance. below is the macro where i send a first original mail from excel.now i want to add follow up mail for which i send from this code.the replying mail should consist of the original "from" address and subject,date and time
is this possible?please suggest

Private Sub Worksheet_Change(ByVal Target As Range) Dim selectedValue As String Dim templateValues As String Dim fileLocation As String Dim personName As String Dim Subject As String
selectedValue = ActiveCell.Formula
On Error GoTo SubEnd templateValues = Application.WorksheetFunction.VLookup(selectedValue, Sheets("Template").Range("A1:A30"), 1, False) fileLocation = Application.WorksheetFunction.VLookup(selectedValue, Sheets("Template").Range("A1:B30"), 2, False) Subject = Application.WorksheetFunction.VLookup(selectedValue, Sheets("Template").Range("A1:C30"), 3, False) personName = Cells(Application.ActiveCell.Row, 6).Value & " " & Cells(Application.ActiveCell.Row, 7).Value
If selectedValue = templateValues Then Call Client_Mails(fileLocation, Subject)
End If SubEnd: End Sub
Sub Client_Mails(fileLocation As String, Subject As String)
Dim MyOlApp As Object
    'Dim MyItem As Outlook.MailItem
    Dim AttachName As String
    Dim SendTo As String
    Dim FirstName As String
    Dim CompanyName As String
    Dim Sys_Date As String
       

 
    'Sheets("gene").Select
   
    SendTo = Cells(Application.ActiveCell.Row, 9).Value
   
    FirstName = Cells(Application.ActiveCell.Row, 6).Value
    CompanyName = Cells(Application.ActiveCell.Row, 1).Value
   
    Set MyOlApp = CreateObject("Outlook.Application")
     
    Set MyItem = MyOlApp.CreateItemFromTemplate(fileLocation)
   
   
    On Error Resume Next
    With MyItem
        .Display
        .To = SendTo
        .BCC = ""
        .Subject = Subject
        .HTMLBody = Replace(.HTMLBody, "<<Company1>>", CompanyName)
        .HTMLBody = Replace(.HTMLBody, "<<Company2>>", FirstName)
               
    End With
    On Error GoTo 0

    Set MyItem = Nothing
    Set MyOlApp = Nothing
   
   
End Sub
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rdy123 rdy
rdy123 rdy

ASKER

thanks a lot byundt..