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
Bookmarks