Set a range of cells to a variable

W

wes mcallister

New Member
Joined
May 19, 2011
Messages
4
I would like to take a range of cells and insert them into an email that is automaticly created in a macro.

Range(A22, AH59) I want to = a string? then in the body of the email I could insert the string.

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim Req As String
Dim qty1 As String

qty1 = Range("E21: AH59).Text





Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "Hi Team" & vbNewLine & vbNewLine & _
"I have created a new Work Order" & vbNewLine & _
"The Work Order # is " & Range("o3") & vbNewLine & _
"It is to " & Range("C11") & vbNewLine & _
"I would like to order the following parts" & vbNewLine & _
"" & qty1

Any ideas on how to do this.

I can get 1 cell to work but not a range of cells

Thanks in advance
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Hi,

http://www.rondebruin.nl/mail/folder3/mail2.htm

explains how to mail the active worksheet in the body of a mail.

If you are just sending a range then you could create a new sheet copy the range to it and send that as the active worksheet.

I have used cell concatenation here but it may be too untidy if the cell content varies in length a lot cell by cell.

Code: 
FirstRow = 21
LastRow = 59
FirstCol = 5 'E
LastCol = 34 'AH
 
For r = FirstRow To LastRow
For c = FirstCol To LastCol
For Each cell In Cells(r, c)
strtable = strtable & "  " & cell.Value
Next
Next
strtable = strtable & vbNewLine
Next
 
MsgBox strtable ' visual on strtable
 
 
strbody = "Hi Team" & vbNewLine & vbNewLine & _
"I have created a new Work Order" & vbNewLine & _
"The Work Order # is " & Range("o3") & vbNewLine & _
"It is to " & Range("C11") & vbNewLine & _
"I would like to order the following parts" & vbNewLine _
[COLOR=red]& vbNewLine & strtable[/COLOR]
 
Upvote 0
I have coded this using HTML tags.
The end result is a lot better appearance in Outlook as the data is tabulated.

HTML: 
Sub mail_text_html()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim Req As String
Dim qty1 As String

FirstRow = 21
LastRow = 28
FirstCol = 5 'E
LastCol = 16 ' H
For r = FirstRow To LastRow
For c = FirstCol To LastCol
For Each cell In Cells(r, c)
strtable = strtable & "<td>" & cell.Value & "</td>"
Next
Next
MsgBox strtable 'check content
strtableRow = strtableRow & vbNewLine & "<tr>" & strtable & "</tr>"
'MsgBox strtableRow 'check content
strtable = ""
Next
strbody = "Hi Team, <br/ ><br/ > I have created a new Work Order <br/ > The Work Order # is " & Range("o3") & "." & "<br/ > It is to " & Range("C11") & "<br/ >I would like to order the following parts"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
 On Error Resume Next
        With OutMail
            .Subject = EmailSubject
            .To = EmailSendTo
            'Set body format to HTML
            .BodyFormat = olFormatHTML
            .HTMLBody = strbody & "<head><body><br /><br /><table>" & strtableRow & "</table></body></head>"
            .Display
           '.Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub
 
Upvote 0
... you can modify the tags for instance add a border to the table, change fonts etc to tart up the message.

Search.

.HTMLBody
 
Upvote 0
So everything works great. today we switched from 2003 to 2010 and now the email will only send if outlook is open.

Is there a way around this?
 
Upvote 0
Hi Wes,

not used 2010 so I'm not sure.

You could use the CDO mail system.
http://www.rondebruin.nl/cdo.htm


Here's your example:


HTML: 
Sub CDO_Mail_HTML()
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
Dim Flds As Variant
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
iConf.Load -1 ' CDO Source Defaults

Set Flds = iConf.Fields
With Flds
.Item("http://schemas.microsoft.com/cdo/connection/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "USERNAME" ' mail account username
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "PASSWORD" 'mail account password
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "MAIL SERVER" ' e.g mail.btinternet.com
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Item("http://schemas.microsoft.com/cdo/connectiontimeout") = 30
.Update
End With
FirstRow = 21
LastRow = 28
FirstCol = 5 'E
LastCol = 16 ' H
For r = FirstRow To LastRow
For c = FirstCol To LastCol
For Each cell In Cells(r, c)
strtable = strtable & "<td>" & cell.Value & "</td>"
Next
Next
strtableRow = strtableRow & vbNewLine & "<tr>" & strtable & "</tr>"
strtable = ""
Next
strbody = "Hi Team, <br/ ><br/ > I have created a new Work Order <br/ > The Work Order # is " & Range("o3") & "." & "<br/ > It is to " & Range("C11") & "<br/ >I would like to order the following parts"

With iMsg
Set .Configuration = iConf
.To = "MAIL TO ADDRESS"
.CC = ""
.BCC = ""
.From = "MAIL FROM ADDRESS"
.Subject = "HTML message"
'.TextBody = strbody
.HTMLBody = strbody & "<head><body><br /><br /><table Border = 1>" & strtableRow & "</table></body></head>"
.Send
MsgBox ("msg gone")
End With
End Sub
 
Last edited:
Upvote 0
To use:

In the VBA editor select Tools>Referneces and tick the box
'Microsoft CDO for Windows 2000 library'
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
VBA sending an email of two conditions are met
Replies
5
Views
241
mumps
mumps
H
  • Question Question
Macro to email Report
Replies
2
Views
577
howard
H
A
  • Question Question
Add Outlook "Confidential" Sensitivity Label
Replies
2
Views
162
Aviles
A
S
  • Solved
Tweak to VBA email code
Replies
3
Views
795
rollis13
rollis13
B
  • Solved
VBA copy Values exclude 1 column and formula
Replies
2
Views
303
bombergirl61
B

Forum statistics

Threads
1,224,568
Messages
6,179,595
Members
452,927
Latest member
whitfieldcraig

Share this page

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
Back
Top