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.WorksheetFunct ion.VLooku p(selected Value, Sheets("Template").Range(" A1:A30"), 1, False) fileLocation = Application.WorksheetFunct ion.VLooku p(selected Value, Sheets("Template").Range(" A1:B30"), 2, False) Subject = Application.WorksheetFunct ion.VLooku p(selected Value, Sheets("Template").Range(" A1:C30"), 3, False) personName = Cells(Application.ActiveCe ll.Row, 6).Value & " " & Cells(Application.ActiveCe ll.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.ActiveCe ll.Row, 9).Value
FirstName = Cells(Application.ActiveCe ll.Row, 6).Value
CompanyName = Cells(Application.ActiveCe ll.Row, 1).Value
Set MyOlApp = CreateObject("Outlook.Appl ication")
Set MyItem = MyOlApp.CreateItemFromTemp late(fileL ocation)
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
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.WorksheetFunct
If selectedValue = templateValues Then Call Client_Mails(fileLocation,
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.ActiveCe
FirstName = Cells(Application.ActiveCe
CompanyName = Cells(Application.ActiveCe
Set MyOlApp = CreateObject("Outlook.Appl
Set MyItem = MyOlApp.CreateItemFromTemp
On Error Resume Next
With MyItem
.Display
.To = SendTo
.BCC = ""
.Subject = Subject
.HTMLBody = Replace(.HTMLBody, "<<Company1>>"
.HTMLBody = Replace(.HTMLBody, "<<Company2>>"
End With
On Error GoTo 0
Set MyItem = Nothing
Set MyOlApp = Nothing
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER