Page 3 of 3 FirstFirst 1 2 3
Results 21 to 22 of 22

Thread: Vba to copy cells and paste into outlook

  1. #21
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    Excel Office 365 Insider

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

    Hi MrT,

    If you're pushing the data from Excel, I'd probably use Excel, not Outlook to host the code. We can use VBA's SaveCopyAs method to make a copy of the active workbook so we can attach it. Or we can push a range from Excel into an email as well. My recommendation would be to start a new thread that outlines what you want to do. (More visibility than tacking on an old one.)

    Oh, and sample workbooks can be attached via the advanced posting interface. (Click Go Advanced at the bottom.)
    Ken Puls, FCPA, FCMA, MS MVP

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

    Main Site: -||- Blog: -||- 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.

  2. #22
    Neophyte Memorex's Avatar
    Join Date
    Sep 2018
    Excel Version
    Excel Version 2010


    Hi Ken,

    I need your help that is similar to the original thread code.

    Here is my problem, I want the selected cell to be left indent and the text(cell) format as per my email body format (ie font, color, size).

    As for your information, the cell I wanted to select is in the middle of the table as shown in below picture.

    Click image for larger version. 

Name:	VBA Selected Cell Column.JPG 
Views:	3 
Size:	35.0 KB 
ID:	8473

    And I want my end result to be like this.

    Click image for larger version. 

Name:	VBA desired outcome.PNG 
Views:	8 
Size:	19.3 KB 
ID:	8471

    Problem is, when this code run, below is the outcome I received.

    Click image for larger version. 

Name:	VBA Current Outcome.JPG 
Views:	8 
Size:	53.3 KB 
ID:	8474

    I am very new to excel VBA and I am using Microsoft Outlook and Excel Version 2010.

    Below is my code and I hope you could help me.

    Sub SendSelectedCellsAsReplyAll_inOutlookEmail()
        Dim objSelection As Excel.Range
        Dim objTempWorkbook As Excel.Workbook
        Dim objTempWorksheet As Excel.Worksheet
        Dim strTempHTMLFile As String
        Dim objTempHTMLFile As Object
        Dim objFileSystem As Object
        Dim objTextStream As Object
        Dim objOutlookApp As Outlook.Application
        Dim objNewEmail As Outlook.MailItem
        Dim xOutMsg As String
        Dim mail 'object/mail item iterator
        Dim replyAll 'object which will represent the reply email
        'Copy the selection
        Set objSelection = Selection
        'Paste the copied selected ranges into a temp worksheet
        Set objTempWorkbook = Excel.Application.Workbooks.Add(1)
        Set objTempWorksheet = objTempWorkbook.Sheets(1)
        'Paste the selection
        With objTempWorksheet.Cells(1)
        Set objSelection = Selection
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteColumnWidths
        End With
        'Save the temp worksheet as a HTML file
        Set objFileSystem = CreateObject("Scripting.FileSystemObject")
        strTempHTMLFile = objFileSystem.GetSpecialFolder(2).Path & "\Temp for Excel" & Format(Now, "YYYY-MM-DD hh-mm-ss") & ".htm"
        Set objTempHTMLFile = objTempWorkbook.PublishObjects.Add(xlSourceRange, strTempHTMLFile, objTempWorksheet.Name, objTempWorksheet.UsedRange.Address)
        objTempHTMLFile.Publish (True)
        'Create a new email
        Set objOutlookApp = CreateObject("Outlook.Application")
        Set objNewEmail = objOutlookApp.CreateItem(olMailItem)
        'Read the HTML file data and insert into the email body
        Set objTextStream = objFileSystem.OpenTextFile(strTempHTMLFile)
        For Each mail In Outlook.Application.ActiveExplorer.Selection
        If mail.Class = olMail Then
            Set replyAll = mail.replyAll
            With replyAll
                'Set body format to HTML
                 .HTMLBody = objTextStream.readall & vbCrLf & replyAll.HTMLBody
                 xOutMsg = "<span style=""color:#1F497D"">Hi</span style=""color:#1F497D""><br />" & _
                           "<p style='font-family:calibri;font-size:11pt'></p>"
                  .HTMLBody = xOutMsg & vbCrLf & replyAll.HTMLBody
            End With
        End If
        objTempWorkbook.Close (False)
        objFileSystem.DeleteFile (strTempHTMLFile)
    End Sub

Page 3 of 3 FirstFirst 1 2 3

Posting Permissions

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