Results 1 to 4 of 4

Create outlook 2016 message using excel 2016 data

Threaded View

  1. #1
    Registered User
    Join Date
    05-31-2013
    Location
    Chisinau
    MS-Off Ver
    Excel 2016
    Posts
    25

    Create outlook 2016 message using excel 2016 data

    Please help me with the following

    I have an xlsx file with 2 sheets (Sheet1 - list with contacts, list 2 - the text).

    I need a macro which would create a new outlook message with the following parameters:
    to: the email address from the active cell
    body: the text from sheet 2 which also contains a table (it's important the table to be in the message body below the text from Sheet2.A3).

    I found the code mentioned below but it does not work when I'm adding Worksheets("sheet2").Range("a6:e10") (Run-time error '13'/ Type mismatch)

    xMailBody = Worksheets("sheet2").Range("A3") & Worksheets("sheet2").Range("a6:e10")
    Dim xRg As Range
    Private Sub Worksheet_Change(ByVal Target As Range)
        On Error Resume Next
        If Target.Cells.Count > 1 Then Exit Sub
      Set xRg = Intersect(Range("D7"), Target)
        If xRg Is Nothing Then Exit Sub
        If IsNumeric(Target.Value) And Target.Value > 200 Then
            Call Mail_small_Text_Outlook
        End If
    End Sub
    Sub Mail_small_Text_Outlook()
        Dim xOutApp As Object
        Dim xOutMail As Object
        Dim xMailBody As String
        Set xOutApp = CreateObject("Outlook.Application")
        Set xOutMail = xOutApp.CreateItem(0)
        xMailBody = Worksheets("sheet2").Range("A3") & Worksheets("sheet2").Range("a6:e10")
        On Error Resume Next
        With xOutMail
            .To = ActiveCell.Value
            .CC = ""
            .BCC = ""
            .Subject = "important message"
            .Body = xMailBody
            .Display   'or use .Send
        End With
        On Error GoTo 0
        Set xOutMail = Nothing
        Set xOutApp = Nothing
    End Sub
    The file is attached
    Attached Files Attached Files
    Last edited by Cephei; 10-29-2021 at 08:50 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 11-20-2019, 04:05 AM
  2. Macro to create multiple accounts in Outlook 2016
    By nobleprince in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-01-2018, 11:32 AM
  3. [SOLVED] No value given for one or more parameters - Access 2016 - Excel 2016
    By schulzy175 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-18-2018, 10:11 PM
  4. Replies: 1
    Last Post: 09-18-2017, 08:27 AM
  5. [SOLVED] Issue send email with excel 2016 true outlook 2016
    By kirana2014 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-12-2017, 11:34 AM
  6. Help! Automated Email coding for Excel 2016 and Outlook 2016 problems
    By lisa6421 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-01-2017, 08:07 AM
  7. Copy /paste excel data with mouse over comments to outlook 2016.
    By Rakeshkrishnan01 in forum Excel General
    Replies: 3
    Last Post: 06-28-2017, 02:24 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1