VBA for Emailing Excel Sheet

FLOGINEEDHELP

New Member
Joined
Apr 16, 2011
Messages
5
Hi,

I currently have the code below which sends the open excel sheet to an address which is hard coded into the VBA code. However i want the address to be one which comes from the excel sheet itself, so a cell reference, is this possible.

I have attached part of the code below which is affected

thanks in advance

HTML:
 Sub Mail_workbook_Outlook_1()

    Dim Cell As Range
    Dim strbody As String
    For Each Cell In ThisWorkbook.Sheets("FILE NAME").Range("FILE RANGE")
        strbody = strbody & Cell.Value & vbNewLine
    Next
 
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
 
    On Error Resume Next
    With OutMail
        .To = "EMAIL ADDRESS"
        .CC = ""
        .BCC = ""
        .SUBJECT = "FILE NAME"
        .Body = strbody
        .Attachments.Add ActiveWorkbook.FullName
              .Send   'or use .Display
    End With
    On Error GoTo 0
 
    Set OutMail = Nothing
    Set OutApp = Nothing
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
You need to declare the cell as you are doing with others.

add something like this

Dim rng as Range
rng = range("B3") 'Change id
in the send to

.To = rng

You are actually sending the whole workbook unless it contains a single sheet?

If you want to use a loop to a list then perhaps look at the DataPig website as there is a visual tutorial you can watch.

http://datapigtechnologies.com/flashfiles/excelsendemail.html
 

FLOGINEEDHELP

New Member
Joined
Apr 16, 2011
Messages
5
thanks for the feedback....

I've tried the below, but keep getting an error saying "object variable or with block variable not set",

sorry, havent used VBA for a while and im trying to modify an old sheet

HTML:
 Sub Mail_workbook_Outlook_1()

    Dim Cell As Range
    Dim strbody As String
    Dim rng As Range
    For Each Cell In ThisWorkbook.Sheets("FILE NAME").Range("FILE RANGE")
        strbody = strbody & Cell.Value & vbNewLine
    Next
    
    rng = Range("C4") 
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = "EMAIL ADDRESS"
        .CC = ""
        .BCC = ""
        .SUBJECT = "FILE NAME"
        .Body = strbody
        .Attachments.Add ActiveWorkbook.FullName
              .Send   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
Ok question is this supposed to loop through a range of cells or just refer to a single cell.

What does Range("FILE RANGE") refer to, is this a named range?

I haven't checked this but highlighted where this range should go, if you aren't using CC and BCC why add them in your code, no need to!


Sub Mail_workbook_Outlook_1()

Dim Cell As Range
Dim strbody As String
Dim rng As Range
For Each Cell In ThisWorkbook.Sheets("FILE NAME").Range("FILE RANGE")
strbody = strbody & Cell.Value & vbNewLine
Next

rng = Range("C4")
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = rng
.CC = ""
.BCC = ""
.Subject = "FILE NAME"
.Body = strbody
.Attachments.Add ActiveWorkbook.FullName
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub
 
Last edited:

FLOGINEEDHELP

New Member
Joined
Apr 16, 2011
Messages
5

ADVERTISEMENT

Hi,

thanks for the response...In answer to your questions,

1) its just a single cell
2) Nope, its just a range of cells so, Range("BB1:BC6")

I have ammedned the code as below, but am still getting the same error???

any ideas???

thanks
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
Look at this example, Remember you need to have the references set to use Outlook in the VBA screen

Part of the code you will see it is using send keys that is the same as pasting into the body of the email. Example spreadsheet data

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 165px"><COL style="WIDTH: 64px"><COL style="WIDTH: 112px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Email Address</TD><TD> </TD><TD>Body of Message</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="COLOR: #0000ff; TEXT-DECORATION: underline">trevor@somewhere.com</TD><TD> </TD><TD>Help</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD> </TD><TD> </TD><TD>Can</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD> </TD><TD>I Send</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Subject</TD><TD> </TD><TD>This Email</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>Sending This</TD><TD> </TD><TD>Yes I Can</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Then the code used

Sub EMailThis()
Range("E3:E8").Copy
Dim olApp As Outlook.Application
Dim olMail As MailItem
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
With olMail

.To = Range("c4").Value
.Subject = Range("C8").Value
'.Attachments.Add ActiveWorkbook.FullName
.Display
SendKeys "^({v})", True 'This is the same as using Paste
End With

Set olMail = Nothing
Set olApp = Nothing
End Sub
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
I hope it helps find the solution.

Thanks for letting me know. ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,609
Members
414,080
Latest member
penguin23

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