Pasting range as text value to email

L

LouD86

New Member
Joined
Aug 14, 2018
Messages
3
Hi all,

I've learnt everything I know about VBA (not a lot) from Google it's left me stumped on my latest attempt! I can see some suggested solutions but none of them really fit my scenario!

I have a worksheet that details team performance across columns A - AH. The columns of relevance for this query are C, D, O and V - O being where I plan to make my selection of cells (likely 6 cells/rows at a time) to enable the macro.


I have a code that I have attached to a button to generate an email deigned as a business update. I've covered almost everything I need including inserting an image, counting active cells, summing offset active cells etc but I'm struggling to directly transpose offset rages into the text of the email.

Essentially, I have three columns that I need to insert the data (they're names) as text values that I can format into a horizontal list.

Here is a sanitized example of my code.


Code: 
[FONT=Calibri][SIZE=3][COLOR=#000000]Sub Internal_RoundUp()[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] 
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim xOutApp As Object[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=3][COLOR=#000000]Dim xOutMail As Object[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim xMailBody As String[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Set xOutApp =CreateObject(""Outlook.Application"")[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Set xOutMail =xOutApp.CreateItem(0)[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]xMailBody =  [/COLOR][/SIZE][SIZE=3][COLOR=#000000]"<imgsrc='cid:test.jpg' height="170" "="" &="" "width="650">
" & "
" & "
"&</imgsrc='cid:test.jpg'>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]"[B]" & "Welcome to the " &format(dateadd("m",-1,date),"mmmm") & " edition ofthe Mid Market Sales Round Up" & [/B]" &"
" & "
" & "This month hasseen " & selection.cells.count & " deals complete" &"
" & "
" &[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=red][FONT=Calibri][SIZE=3][INSERT NAMES       [/SIZE][SIZE=3]offset C (-12)] [/SIZE][/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]& " are our newest clients and have a total product value of" & "[B]" & "£" &application.worksheetfunction.sum(offset(selection,-1)) &"[/B]" & " including prod 1 totalling " &"[B]" & "£" &application.worksheetfunction.sum(offset(selection,-4)) &"[/B]" & " and prod 2 at " &"[B]" & "£" &application.worksheetfunction.sum(offset(selection,-2)) &"[/B]" & ". That equates to " &"[B]" & "£" &application.worksheetfunction.sum(selection) & "[/B]" &" PFI." & "
" & "
"& "Our BD's, " & [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=red][FONT=Calibri][SIZE=3][INSERT NAMES      [/SIZE][SIZE=3]offset D (-11)][/SIZE][/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]& " would like toextend their thanks to " & "[B]" & [/B][/COLOR][/SIZE][/FONT][B]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=red][FONT=Calibri][SIZE=3][INSERT THANK YOU NAMES      [/SIZE][SIZE=3]offset [/SIZE][SIZE=3]V(+7)] [/SIZE][/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]& "[/COLOR][/SIZE][/FONT][/B][SIZE=3][COLOR=#000000]" & " for their help in gettingthese deals over the line ." [/COLOR][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]On Error Resume Next[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]With xOutMail[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].To =""person@business.co.uk""[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].CC =""""[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].BCC =""""[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].Subject =""Mid Market Round up!""[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].BodyFormat =olFormatHTML[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].Attachments.Add""V:\Sales\Deal Alerts\Images\test.jpg"", olByValue, 0[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]sImgName =""test.jpg""[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].htmlbody = xMailBody[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].Display[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]On Error GoTo 0[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Set xOutMail = Nothing[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Set xOutApp = Nothing[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]



Any suggestions (in their simplest form as I’m extraordinarilysurprised I’ve made it this far on my knowledge!) would be greatly appreciated!


Thanks

 
Sort by date Sort by votes
Not sure what happened to my code but a chunk of it appears to be missing/spaced incorrectly! Not sure what I'm doing wrong but will have another go!

Code: 
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Sub Internal_RoundUp()[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][SIZE=3][COLOR=#000000]Dim xOutApp As Object[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][SIZE=3][COLOR=#000000]Dim xOutMail As Object[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][SIZE=3][COLOR=#000000]Dim xMailBody As String[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][SIZE=3][COLOR=#000000]Set xOutApp =CreateObject(""Outlook.Application"")[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][SIZE=3][COLOR=#000000]Set xOutMail =xOutApp.CreateItem(0)"[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]xMailBody =  [/COLOR][/SIZE][SIZE=3][COLOR=#000000]"<imgsrc='cid:test.jpg' & "width='650' height='170'" & "><br>" & "<br>" & "<br>"&[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]"<b>" & "Welcome to the " &format(dateadd("m",-1,date),"mmmm") & " edition ofthe Mid Market Sales Round Up" & </b>" &"<br>" & "<br>" & "This month hasseen " & selection.cells.count & " deals complete."& "<br>" & "<br>" &[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]‘[INSERT CLIENT NAMES      [/COLOR][/SIZE][SIZE=3][COLOR=#000000]offset-12] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]& " are our newest clients and have a total product  [/COLOR][/SIZE][SIZE=3][COLOR=#000000]value of " & "<b>"& "£" & application.worksheetfunction.sum(offset(selection,-1))& "</b>" & " including prod 1 totalling "& "<B>" & "£" &application.worksheetfunction.sum(offset(selection,-4)) &"</b>" & " and prod 2 at " &"<b>" & "£" &application.worksheetfunction.sum(offset(selection,-2)) &"</b>" & ". That equates to " &"<b>" & "£" &application.worksheetfunction.sum(selection) & "</b>" &" PFI." & "<br>" & "<br>"&[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]"Our BD's, " &[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]‘[INSERT BD NAMES      [/COLOR][/SIZE][SIZE=3][COLOR=#000000]offset-11] [/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]& " would like to extend their thanks to " &"<b>" & [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]‘[INSERT THANK YOU NAMES     [/COLOR][/SIZE][SIZE=3][COLOR=#000000]offset +7] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]& "</b>" & " for their help in gettingthese deals over the line." & "<br>" &"<br>" &[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]On Error Resume Next[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][SIZE=3][COLOR=#000000]With xOutMail[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]        [/COLOR][/SIZE][SIZE=3][COLOR=#000000].To = “Person@business.co.uk”[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]        [/COLOR][/SIZE][SIZE=3][COLOR=#000000].CC =""""[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]        [/COLOR][/SIZE][SIZE=3][COLOR=#000000].BCC =""""[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]        [/COLOR][/SIZE][SIZE=3][COLOR=#000000].Subject =""Mid Market Sales Round up!""[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]        [/COLOR][/SIZE][SIZE=3][COLOR=#000000].BodyFormat =olFormatHTML[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]        [/COLOR][/SIZE][SIZE=3][COLOR=#000000].Attachments.Add""V:\Sales\Deal Alerts\Images\test.jpg"", olByValue, 0[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]        [/COLOR][/SIZE][SIZE=3][COLOR=#000000]sImgName =""test.jpg""[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]        [/COLOR][/SIZE][SIZE=3][COLOR=#000000].htmlbody = xMailBody[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]        [/COLOR][/SIZE][SIZE=3][COLOR=#000000].Display[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][SIZE=3][COLOR=#000000]On Error GoTo 0[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][SIZE=3][COLOR=#000000]Set xOutMail = Nothing[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][SIZE=3][COLOR=#000000]Set xOutApp = Nothing[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]
 
Upvote 0
Just so I understand correctly, you have, for example, 10 values in cells C1:C10, and want them listed in the email as C1, C2, C3, C4, etc.?
 
Upvote 0
Hi,

That's correct. Basically, each of the offset ranges has a name in each cell that I need to list as you've formatted above (Joe Blogg, Jane Blogg, Ernie The Milkman). If this has to be produced as a text only list i.e.
Joe Blogg
Jane Blogg
Ernie The Milkman
with the rest of the content wrapped (similar to this post) then that's fine - still far less formatting required than if they weren't there at all!
 
Upvote 0
Lou, this is a very basic idea of what you can do, but I'm not quite sure why you are using offset. Let me know if something like this would work for you (I've only written it for column C's values):

Code: 
Dim x As Integer
Dim names As String

For x = 1 To 10 'I used 10 just to test it briefly, this should probably be set for the last row of your column
     If Range("C" & x).Value <> 0 Then
          If x = 1 Then
               names = Range("C" & x).Value
          Else
               names = names & ", " & Range("C" & x).Value
          End If
     End If
Next

You should then be able to just set that "[INSERT NAMES OFFSET C(-12)" to names (or whatever you want to call it, this was just an example) and it (hopefully) should list it properly.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Add text from specific cell into body of email
Replies
3
Views
1K
Candyman8019
C
J
  • Solved
VBA - Send email based on a cells value; Include other cells value's from the same line in email subject
Replies
4
Views
1K
Jibsta
J
S
  • Question Question
VBA Question - I want to Auto send an email when a cell value gets populated and it should select the values displayed on the left side of the cell
Replies
3
Views
1K
rollis13
rollis13
J
  • Question Question
Show Cell Value in Email
2
Replies
16
Views
1K
Michael M
Michael M
W
  • Question Question
Automate reply-all (or forward) email and keep original text, then populate with excel data - excel VBA
Replies
1
Views
6K
wiseone
W

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