here's what i am trying to do

[enoch5939]

I am sure that this has been covered, but whenever I try and search for something, I get a time out error, so I am sorry if this has been posted before.

I have found many many articles and references about what I am trying to do, but I guess I just can't seem to figure it out.

Here is what I am trying to do:

Column G is where I list emails. When I currently click on Column G it will open an outlook new mail message.

but there's nothing there, I have to add everything... What I am trying to figure out, is how to get populate 'other' cell information into the email, plus have a "default" message body all ready to go, so all i have to do is hit sent.

in the subject I want to populate columns c,d,e, with their corresponding Heading title in front of each of them.

In the body, I just need the Persons name from column b, and then the default message.

I really hope this makes sense. If anyone can help me, and explain to me in simple terms .. I would most appriciate it ...

James

 

[Norie]

Well the problem really isn't with that code, it's how to adapt it to work with your worksheet/range.

To give you specific help we would need to know how your sheet is set up.

The following will take values from row 1 of the active sheet.

Sub email()
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    strbody = Range("B1") & vbNewLine & vbNewLine & "message here"
    
    With OutMail
        .To = Range("G1")
        .Subject = Range("C1") & Range("D1") & Range("E1")
        .Body = strbody
        .Display 'or use .Display
    End With
    
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[enoch5939]

k, so that will insert the information where it needs to go...

my data starts in Row 3, B3. if i change the values to B3, C3, D3, and E3, and I click on the email link in G3, it will do what it is suppose to do, but if I click on the email link in say.... G45, it shoots up the information for the above values ... How do I fix that??? I think i need something to tell it to use the active row...

This is what i have so far:

Sub email()
'You must add a reference to the Microsoft outlook Library
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    strbody = Range("B3") & vbNewLine & vbNewLine & _
              "message here"
 
    With OutMail
        .To = Range("G3")
        .Subject = "Service Tag: " & Range("C3") & ">" & "Case Number: " & Range("D3") & ">" & "Dps Number: " & Range("E3") & ">"
        .Body = strbody
        .Display 'or use .Display
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[enoch5939]

could an if statement be used at all??? I dunno... this is where i usually get stuck ...

 

[enoch5939]

anyone??? or is this just possible ???

 

[Norie]

enoch

Could you please give some more information?

How exactly is this code being triggered?

 

[enoch5939]

in the Sheet, as a private sub I have this:
(please ignore the other entries)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    If Target.Column = 9 And ActiveCell <> "" And Target.Row > 2 Then
       ThisWorkbook.timezone
    Else
    If Target.Column = 3 Then
       ThisWorkbook.launchrcec
    Else
    If Target.Column = 13 And ActiveCell <> "" Then
       Cancel = True
       ThisWorkbook.launchtracking
    Else
    If Target.Column = 7 Then
       ThisWorkbook.email
    Else
    End If
    End If
    End If
    End If
    
End Sub

In the workbook I have what you posted above...

 

[enoch5939]

So, it's been a week. is there no one out there that can help me with further insight as to how to solve this issue.


Let me recap ... IN Column G, I enter in the users email address.
in column A is the callers name, column b, c, and d, are some unique numbers for them as well. I would like to click on the email address in column G, and have values populated in the "new message" window... The values in column a, b, c, and d.


As it stands right now ... with what I have been given and what I have been able to find out myself. If I click on "ANY" .... email address located in column g, it pulls the information from the first line (B3-G3), because those are the range values that have been set. NO matter what I do, it will only do the "first" row for the range... It will NOT reconize any other value that is inputted or clicked on....

I am sorry if I can't make this anymore clear...

James

 

[Norie]

James

Here's a hint - Target.

That's a parameter passed to the event when you double click.

It represents the cell you've double clicked and you can extract it's row using Target.Row.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim strbody As String
    
    If Target.Column = 7 Then
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(olMailItem)
        strbody = Range("B" & Target.Row) & vbNewLine & vbNewLine & "message here"
     
        With OutMail
            .To = Range("G" & Target.Row)
            .Subject = "Service Tag: " & Range("C" & Target.Row) & ">" & "Case Number: " & Range("D" & Target.Row) & ">" & "Dps Number: " & Range("E" & Target.Row) & ">"
            .Body = strbody
            .Display 'or use .Display
        End With
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    End If
    
End Sub

 

[enoch5939]

Thank you :D

 

[enoch5939]

Is there anyway to have this as a single click instead. Right now i have in nested with some other functions to open on a double click. Now if I double click on it .. i get 2 windows .... a blank message windows with my sig. the second window is the message with all the inputs and values in them ...

Any Ideas ???
Thanks again for all the help ...
James