Results 1 to 5 of 5

Thread: Copy from excel and paste in outlook

  1. #1
    Seeker Mahesh Babu's Avatar
    Join Date
    Nov 2012
    Location
    Hyderabad, Andhra Pradesh, India
    Posts
    7
    Articles
    0

    Post Copy from excel and paste in outlook



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Seeker Mahesh Babu's Avatar
    Join Date
    Nov 2012
    Location
    Hyderabad, Andhra Pradesh, India
    Posts
    7
    Articles
    0

    Exclamation VBA Programming to copy from Excel to Outlook

    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.....!
    Attached Files Attached Files
    Last edited by Mahesh Babu; 2012-11-10 at 03:41 AM. Reason: Missed some infomation

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    Seeker Mahesh Babu's Avatar
    Join Date
    Nov 2012
    Location
    Hyderabad, Andhra Pradesh, India
    Posts
    7
    Articles
    0

    Post

    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,
    Attached Files Attached Files

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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:
    Code:
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •