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

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
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.
HTML:
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
 
Last edited:

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
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
Code:
 If [toEmail] = "" Then
        MsgBox "ToEmail ID is mandatory"
        Exit Sub
    End If
And..
HTML:
    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
 
Last edited:

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
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
Code:
Set RangeToSend = ActiveSheet.Range("D12:N31")

Hope you can help with the 2 queries please.
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
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
Code:
Set RangeToSend = ThisWorkbook.Sheets("Sheet1").Range("D12:N31," & .Range("D" & Rows.Count).End(xlUp)
 

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,887
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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.

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

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
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

Well-known Member
Joined
Jul 9, 2009
Messages
1,887
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
 
Last edited:

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
Hi thank you for the help. The maximum length would be no more than 220 shall I use dim last row as long?
 

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,887
Office Version
  1. 2013
Platform
  1. Windows
Hi,

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

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
Hi Good morning,this works great thanks for the advise and help, much appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,069
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top