There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
 
Business Applications
Tag Cloud
audio blue blue screen boot bsod compaq computer cpu crash dell drivers dvd error excel firefox format freeze freezing hard drive hardware install internet internet explorer kb951748 lan laptop loss of internet malware memory motherboard network networking outlook outlook 2007 problem restart screen security slow sound trojan update virus vista windows windows xp winxp wireless zonealarm zone alarm
Search
Search in:
 
Advanced Search
Tech Support Guy Forums > Software & Hardware > Business Applications >
Excel Cell Email update


Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members. Enjoy!

Closed Thread
 
Thread Tools
rafterman79's Avatar
Junior Member with 11 posts.
 
Join Date: Sep 2008
02-Oct-2008, 11:36 AM #1
Excel Cell Email update
Hi guys,

Thanks for sorting me out last time, however I have another query:

I am using this VB script to email values in cells, the problem is when people have autosignatures with no carriage returns the data in excel is put on the same line as the auto sig, is there a way I can get a carriage return after the data output? I have tried adding Chr(13) (in bold below) but this didnt work.

Any ideas?

Thanks,

Sean

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String

Email = " & "," &

' Message subject
Subj = ""
' Compose the message
thisrow = ActiveCell.Row
Msg = ""
Msg = Msg & Cells(thisrow, 3) & Chr(44) & Space(1) & Cells(thisrow, 4) & Chr(44) & Space(1) & Cells(thisrow, 5) & Chr(44) & Space(1) & Cells(thisrow, 6) & Chr(44) & Space(2) & Cells(thisrow, 7) & Chr(44) & Space(1) & Cells(thisrow, 8) & Chr(44) & Space(1) & Cells(thisrow, 9) & Chr(44) & Space(1) & Cells(thisrow, 10) & Chr(44) & Space(1) & Chr(13)

' Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg & Chr(13), " ", "%20")

' Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, Space(1), "%0D%0A")
' Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg
' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus
End Sub
Rollin_Again's Avatar
Distinguished Member with 2,906 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Brilliant When Sober
02-Oct-2008, 12:12 PM #2
Try using vbCrLf instead of the Chr(13)

Regards,
Rollin
rafterman79's Avatar
Junior Member with 11 posts.
 
Join Date: Sep 2008
02-Oct-2008, 12:19 PM #3
Tried that but on Lotus Notes it does not work.

Any other ways of doing it I could try?
rafterman79's Avatar
Junior Member with 11 posts.
 
Join Date: Sep 2008
02-Oct-2008, 12:30 PM #4
I have noticed that the cursor is at the start of the output on the email.

Dont know if this makes any difference but it might.
rafterman79's Avatar
Junior Member with 11 posts.
 
Join Date: Sep 2008
02-Oct-2008, 02:45 PM #5
Msg = Application.WorksheetFunction.Substitute(Msg & Chr(13), " ", "%20")

What does this bit do? And if I put vbcrlf in do I have to put it in somewhere here?
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 3,549 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
03-Oct-2008, 04:21 PM #6
Hi there,

Maybe try vbNewLine or Chr(10) instead. I believe the "%20" is a space in HTML.

Is that your entire sub routine, what you have posted above? There are some things with it that doesn't make sense to me, some ways you could improve it. Let us know if you'd like us to look at that.
__________________
___________
Regards, Zack (If you would like comments in any code, please say so.)

OfficeArticles.com :|: Extreme Excel Tutorial :|: Excel Articles by Ken Puls :|: Excel User Group, by Nick Hodge

What is a Microsoft MVP? :|: Live Tech Support? Click here
bomb #21's Avatar
Distinguished Member with 6,213 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
04-Oct-2008, 05:37 PM #7
The code is a cut-down of John W's "old faithful", but maybe unfamiliar out of context. It was credited in the original thread.

"%20" is a space in HTML.

Neither Chr(10) nor (13) worked for me; didn't try vbNewLine.
slurpee55's Avatar
Computer Specs
Distinguished Member with 4,517 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
04-Oct-2008, 10:38 PM #8
%0D is a carriage return and %0A is a line feed for Lotus Notes (and in general, and appears in this line of the code
"' Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, Space(1), "%0D%0A") "

I believe you need to make sure that your MIME settings are for quoted-printable for this to work.
__________________
Iowa? I could have sworn this was heaven.
Well, I think I can answer this question most successfully in mime.

My theme song...
Closed Thread

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.



Thread Tools


You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 03:24 PM.
Copyright © 1996 - 2008 TechGuy, Inc. All rights reserved.
Powered by vBulletin, Copyright © 2000 - 2008, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.1.0
Powered by Cermak Technologies, Inc.