asked on

adding to,subject,body for the opened mail instead of creating new mail

Hi,

I am using below code to send the email from excel
but now i already have a code which opens the outlook.

for the below code instead of creating new outlook mail i want to add to,subject,body where the mail is already opened.

is this possible,please suggest.

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
        
 
    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, "&lt;&lt;Company1&gt;&gt;", CompanyName)
        .HTMLBody = Replace(.HTMLBody, "&lt;&lt;Company2&gt;&gt;", FirstName)
        .Text = " abc "
        .Replacement = Replace(.Replacement, "abc", Cells(Application.ActiveCell.Row, 9))
        
    End With
    On Error GoTo 0

    Set MyItem = Nothing
    Set MyOlApp = Nothing
    
    
End Sub

Open in new window

thanks in advance

Joe Howard

I'm not sure I understood your request.
If you want to use the open instance of Outlook instead of creating a new one, use the GetObject method:

Set MyOlApp = GetObject(, "Outlook.Application")

Open in new window

instead of the CreateObject method:

Set MyOlApp = CreateObject("Outlook.Application")

Open in new window

Qlemo

MacroShadow,
though that is true for anything else, it is not for Outlook - you always run a single instance at all time only, so it doesn't make a difference if you use CreateObject or GetObject for getting an Outlook.Application object.

rdy123 rdy,
I assume you want to access the mail already (manually or by other processes) opened in Outlook. The currently opened mail (with an Window) is accessed with

Set MyItem = MyOlApp.ActiveInspector.CurrentItem

Open in new window

Joe Howard

I like this one, clear concise and complete with error handling.

Qlemo

A little off-topic, that link: The code runs in Outlook VBA, and it acts on the body (to insert text from another mail).

Joe Howard

Indeed, but it does show the correct approach in a very clear manner ;-)

rdy123 rdy

ASKER

Hello All,

I am still stuck with this, can anyone please help me out.

Qlemo

What is the issue with my comment in https:#a41935995, you only need to access your item that way, and then set what fields you want to set?! Or am I assuming wrongly what you want to do? In any case, you need to provide more detail for us to be able to help you.

rdy123 rdy

ASKER

hi,

i am getting run time error91 for https:#a41935995,

Qlemo

MyItem is defined as Outlook.MailItem - is the currently open "object" in Outlook indeed a mail item? You need to have that mail opened as an own window for this to work.
If you need more help, you need to describe how you tested, and show the code you used (to make sure that code is correct).

rdy123 rdy

ASKER

i am using the below code,

Sub Client_Mails1()

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
Dim fileLocation As String
Dim selectedValue As String
Dim templateValues As String
'Dim myitem As Object
Dim Subject As String

selectedValue = ActiveCell.Formula

'Sheets("gene").Select
fileLocation = Application.WorksheetFunction.VLookup(selectedValue, Sheets("Template").Range("A1:B30"), 2, False)
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 MyOlApp = GetObject(, "Outlook.Application")
Set myitem = MyOlApp.ActiveInspector.CurrentItem
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)
.Text = " abc "
.Replacement = Replace(.Replacement, "abc", Cells(Application.ActiveCell.Row, 9))

End With
On Error GoTo 0

Set myitem = Nothing
Set MyOlApp = Nothing

End Sub

i have opened the mail also,

please correct me if the code is wrong

Joe Howard

For starters you need this line (you have it commented out):

Set MyOlApp = GetObject(, "Outlook.Application")

Open in new window

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rdy123 rdy

ASKER

thank you ,will try again

rdy123 rdy

ASKER

its working,thanks a lot both...