Send email from cell range in Sheet2 and copy all data to last row

[Patriot2879]

HI All, good morning, i have this code below which works but at the moment it sends the email from the cells in active sheet, and i want it to send the emails from my range in sheet 2 for example the .To i want to send from sheet 2 and B1, and at the moment i have named my range to screenshot and send for example D12:N31 but i want it to be D12:N and to the last row as data gets added daily, can you help me please with this.

Sub SendHTML_And_Image_As_Body_UsingOutlook()

    Dim olApp As Object
    Dim NewMail As Object
    Dim ChartName As String
    Dim imgPath As String
    
    On Error GoTo err
    
    If [toEmail] = "" Then
        MsgBox "ToEmail ID is mandatory"
        Exit Sub
    End If
    
    Set olApp = CreateObject("Outlook.Application")
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    

    tmpImageName = VBA.Environ$("temp") & "\tempo.jpg"
    

    Set RangeToSend = ActiveSheet.Range("D12:N31")

    RangeToSend.CopyPicture Appearance:=xlScreen, Format:=xlPicture
    

    
    Set sht = Sheets.Add
    sht.Shapes.AddChart
    sht.Shapes.Item(1).Select
    Set objChart = ActiveChart

    With objChart
        .ChartArea.Height = RangeToSend.Height
        .ChartArea.Width = RangeToSend.Width
        .ChartArea.Fill.Visible = msoFalse
        .ChartArea.Border.LineStyle = xlLineStyleNone
        .Paste
        .Export Filename:=tmpImageName, FilterName:="JPG"
    End With

    sht.Delete

    Set NewMail = olApp.CreateItem(0)
    
    With NewMail
        .subject = [subject]
        .To = ThisWorkbook.Sheets("Sheet2").Range("B1").Value
        If [ccEmail] <> "" Then .CC = ThisWorkbook.Sheets("Sheet2").Range("B3").Value
        

        .HTMLBody = "******>Dear Sir/Madam, <br/><br/>Kindly find the report below:" & _
        "<br/><img src=" & "'" & tmpImageName & "'/><br/>Regards,<br/>Saturday JM </body>"
        .Display
        
    End With
    MsgBox "Email Sent successfully"

err:

    Set olApp = Nothing
    Set NewMail = Nothing
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

 

[Patriot2879]

Re: how to send email from cell range in Sheet2 and copy all data to last row

Hi the email part i want the below to look into Sheet2 if yu can advise please

 If [toEmail] = "" Then
        MsgBox "ToEmail ID is mandatory"
        Exit Sub
    End If

And..

    With NewMail
        .Subject = [Subject]
        .To = ThisWorkbook.Sheets("Sheet2").Range("B1").Value
        If [ccEmail] <> "" Then .CC = ThisWorkbook.Sheets("Sheet2").Range("B3").Value
        

        .HTMLBody = "******>Dear Sir/Madam, 

Kindly find the report below:" & _
        "

Regards,
Saturday JM "
        .Display
        
    End With

 

[Patriot2879]

Re: how to send email from cell range in Sheet2 and copy all data to last row

And the code below i would like it to goto last line please

Set RangeToSend = ActiveSheet.Range("D12:N31")

Hope you can help with the 2 queries please.

 

[Patriot2879]

Re: how to send email from cell range in Sheet2 and copy all data to last row

Hi i have tried this code to goto the last row but the line goes red and doesnt work please can you help

Set RangeToSend = ThisWorkbook.Sheets("Sheet1").Range("D12:N31," & .Range("D" & Rows.Count).End(xlUp)

 

[daverunt]

Re: how to send email from cell range in Sheet2 and copy all data to last row

Hi,

you can get the last row as a variable then use it in the range.
It may be possible in a single line of code - but not by me.

I have assumed from your posts that column N varies.

LastRow = ThisWorkbook.Sheets("Sheet1").Range("N" & Rows.Count).End(xlUp).Row
Set RangeToSend = ThisWorkbook.Sheets("Sheet1").Range("D12:N" & LastRow)

 

[Patriot2879]

Re: how to send email from cell range in Sheet2 and copy all data to last row

Thanks for your time and help. I shall try this tomorrow morning. What do you mean last row as a variable? Do I need to do a dim on the code? Still quite new to this

 

[daverunt]

Re: how to send email from cell range in Sheet2 and copy all data to last row

Hi,

LastRow - is just a variable used as a store for the row number of the last filled row in column N.

For good coding practice you should Dim all variables.....
https://exceloffthegrid.com/do-you-have-to-use-dim-to-declare-variables/

The variable types are listed here.
https://www.guru99.com/vba-data-types-variables-constant.html

What is the maximum the variable LastRow 'could be' and which Dim do you use?
Dim LastRow As Long
Dim LastRow As Integer

 

[Patriot2879]

Hi thank you for the help. The maximum length would be no more than 220 shall I use dim last row as long?

 

[daverunt]

Hi,

Long would be the correct Type due to the number of rows in Excel exceeding type Integer.

 

[Patriot2879]

Hi Good morning,this works great thanks for the advise and help, much appreciated.