Copy from excel and paste in outlook

[Mahesh Babu]

Hi All,

If i click on a button in excel the required information should be pasted in outlook(Email)


If i enter data in Input sheet and click on submit button the desired output will come in Email sheet. But i want a macro which automatically open an outlook email and and the information in the "Email sheet" should be copied in the same format the moment i click on submit button in "Input Sheet"

Can you help me in creating a macro. *****URGENT HELP***


I have attached the sheet for your reference.

[Mahesh Babu]

Hi Gurus,

(Help)!!!!!! I have data in excel as below:

Banker Name	ABC
Job ID	1234
Documents/Information
	Name of the Client	Required	for the Year
Client # 1	Microsoft	MS	2012
Client # 2
Client # 3
Other Comments	ASAP
(if any)

I want a macro in such a way that if i click on a button i want the output in the body of the email in the below format:

Body of the mail:

Greetings ABC,
I am contacting you regarding the missing information/documents of this client. Request you to provide the same.
Job ID				:-	1234
Required Information				:-
Client/ Information/ Year				:-	Microsoft MS for the year 2012
Comments				:-	ASAP
Thank You,

Subject Line in email: ABC - 1234 - Microsoft


I have written 2 macros:
1.Which is able to generate the output in different sheet.
2.Output will get generated in different sheet, will get copied and the mail will get opened(using hyperlink) but not getting pasted in body of the mail. I have attached excel sheet for your reference.

Need experts help.....!

[Ken Puls]

Hi Mahesh,

I've merged these two threads together since they are identical.

I understand what you're trying to do here, and it can certainly be done, but I do have a question for you. The table that you want to write to the email, does it have to go as a table, or not?

If it does, then I think it pretty much needs to be formatted as an HTML email, with a table built on the fly. Certainly possible, it's just kind of a pain, as someone has to first build the framework, then pull in the key components.

I can't promise that I'm going to be able to meet any urgent deadline you have (I've got a ton of my own), but I'd want to know which parts of the output to Outlook are fixed (will never change) and which parts (cells) will be updated.

[Mahesh Babu]

Hi Ken,

Thanks for your reply. I have written a code with the help of experts like you.

But i have 2 problems still:

1. Want to BOLD and underline the below words in the email. I am not getting the syntax for how to bold/underline a word in VBA.

Client/Information/Year :

Required Information :-

2. The code is written in such a way that, need to run macro for every row. Problem is if i have multiple client's am not getting the output in correct format.( if i have multiple client in example sheet format, then i want the output as below)

Please refer to the attachment for your reference.

In case of multiples clients i need the output as below:

OUTPUT:

Greetings ABC,

I am contacting you regarding the missing information/documents of this client. Request you to provide the same.

Job ID :- 13353
:- 1178838
Required Information :-

Client/Information/Year : IBM Laptop Manual for the year 2011
: Microsoft Processor Version for the year 2012
Comments : Need Original
: 2 Copies

Thank You,

[Ken Puls]

Okay, well, here's the issue. The email you're creating is in plain text. In order to make it show up in tabular format with bold and such, you're going to need to flip to HTML.

I wouldn't normally loop the way you have, but I know you've already waited a few days for help, so I didn't waste time cleaning that up. Give this a shot:

Sub Mail()
'Macro Purpose: To send an email through Outlook
    Dim objOL As Object
    Dim objMail As Object
    Dim sEmail As String
    Dim sEmailColumn As String
    Dim sSubject As String
    Dim sBody As String
    Dim lDataRow As Long
    Dim cl As Range
    'Set column with email address
    sEmailColumn = "J"
    For Each cl In Selection.Resize(, 1)
        'Generate required info
        lDataRow = cl.Row
        'Check if Mail
        If cl.Parent.Range("D" & lDataRow).Value = "Greetings" Then
            With cl.Parent
                sEmail = .Range(sEmailColumn & lDataRow)
                sSubject = "JOB ID " & .Range("B" & lDataRow)
                sBody = "<p>Greetings " & .Range("A" & lDataRow).Value & ",</p>" & _
                        "<p>I am contacting you regarding the missing " & _
                        "information/documents of this client. Request you to provide the same.</p>" & _
                        "<table>" & _
                        "<tr><td>Job ID :-</td><td>" & .Range("E" & lDataRow) & "</td></tr>" & _
                        "<tr><td><b><u>Required Information</u></b> :-</td></tr>" & _
                        "<tr><td><b>Client/Information/Year</b> : </td>" & _
                        "<td>" & .Range("F" & lDataRow) & " " & .Range("G" & lDataRow) & " for the year " & .Range("H" & lDataRow) & "</td></tr>" & _
                        "<tr><td>Comments : </td><td>" & .Range("I" & lDataRow) & "</td></tr>" & _
                        "<tr>: 2 Copies</tr>" & _
                        "</table>"
            End With
            'Turn on error handling
            On Error GoTo Cleanup
            'Bind to Outlook
            Set objOL = CreateObject("Outlook.Application")
            'Create a new email and send it
            Set objMail = objOL.CreateItem(0)    '0=olmailitem
            With objMail
                .To = sEmail
                .Subject = sSubject
                .htmlBody = sBody
                .Display
            End With
        End If
    Next cl
Cleanup:
    'Release all objects
    Set objMail = Nothing
    Set objOL = Nothing
    On Error GoTo 0
End Sub

It's not going to be perfect yet, I'm sure, but it should get you started.