Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to create Email that includes quotes
An error is raised if I try to send quotes to an email body throught code.
I've worked around this by replacing the quotes with an apostrophe, but I was hoping someone out there knows how to pass the quotes through. Thanks to Ron Debruin for most of the code below. http://www.rondebruin.nl/sendmail.htm#selection -Jeremy ------------------------------------------------------------------------- Sub PrepareTheEmail() Dim sRecipient As String Dim sSubject As String Dim sMsg As String Dim sMail As String Const q As String = """" Const apos As String = "'" With ActiveCell sRecipient = .Offset(0, 8) sSubject = "Regarding: " & .Offset(0, 3) sMsg = .Offset(0, 7) & "," & vbNewLine & vbNewLine sMsg = sMsg & "Regarding:" & vbNewLine sMsg = sMsg & .Offset(0, 3) & vbNewLine & vbNewLine sMsg = sMsg & "Details: " & vbNewLine sMsg = sMsg & .Offset(0, 6) & vbNewLine & vbNewLine sMsg = sMsg & "Solution/Comments:" & vbNewLine sMsg = sMsg & .Offset(0, 9) & vbNewLine & vbNewLine sMsg = sMsg & .Offset(0, 10) End With 'Format message to work with Mail program by replacing 'spaces with %20, returns with %0D%0A, quotes with apostrophe With Application.WorksheetFunction sSubject = .Substitute(sSubject, " ", "%20") sMsg = .Substitute(sMsg, " ", "%20") sMsg = .Substitute(sMsg, vbNewLine, "%0D%0A") sMsg = .Substitute(sMsg, vbLf, "%0D%0A") sMsg = .Substitute(sMsg, q, apos) End With sMail = "mailto:" & sRecipient & _ "?subject=" & sSubject & _ "&body=" & sMsg ThisWorkbook.FollowHyperlink sMail End Sub ------------------------------------------------------------------------- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Code to create Email that includes quotes
Hi Jeremy, I saw your original message and thought it was great! This is something that I needed. However, I needed to send an attached file with that and tried a couple of options, as well as passing the command to outlook to automatically send the message so it is transparent to user once they have pressed the submit button (command button) How would I include in the below a file attachement (e.g. the document that I am in executing the code? Your help would be greatly appreciated. Michelle ----- Jeremy Gollehon wrote: ---- An error is raised if I try to send quotes to an email body throught code I've worked around this by replacing the quotes with an apostrophe, but was hoping someone out there knows how to pass the quotes through Thanks to Ron Debruin for most of the code below http://www.rondebruin.nl/sendmail.htm#selectio -Jerem ------------------------------------------------------------------------ Sub PrepareTheEmail( Dim sRecipient As Strin Dim sSubject As Strin Dim sMsg As Strin Dim sMail As Strin Const q As String = """ Const apos As String = "' With ActiveCel sRecipient = .Offset(0, 8 sSubject = "Regarding: " & .Offset(0, 3 sMsg = .Offset(0, 7) & "," & vbNewLine & vbNewLin sMsg = sMsg & "Regarding:" & vbNewLin sMsg = sMsg & .Offset(0, 3) & vbNewLine & vbNewLin sMsg = sMsg & "Details: " & vbNewLin sMsg = sMsg & .Offset(0, 6) & vbNewLine & vbNewLin sMsg = sMsg & "Solution/Comments:" & vbNewLin sMsg = sMsg & .Offset(0, 9) & vbNewLine & vbNewLin sMsg = sMsg & .Offset(0, 10 End Wit 'Format message to work with Mail program by replacin 'spaces with %20, returns with %0D%0A, quotes with apostroph With Application.WorksheetFunctio sSubject = .Substitute(sSubject, " ", "%20" sMsg = .Substitute(sMsg, " ", "%20" sMsg = .Substitute(sMsg, vbNewLine, "%0D%0A" sMsg = .Substitute(sMsg, vbLf, "%0D%0A" sMsg = .Substitute(sMsg, q, apos End Wit sMail = "mailto:" & sRecipient & "?subject=" & sSubject & "&body=" & sMs ThisWorkbook.FollowHyperlink sMai End Su ------------------------------------------------------------------------ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Code to create Email that includes quotes
Michelle,
Sent the following to your other thread... Just in case - here it is again... steveb (Remove 'NOSPAM' from email address if contacting direct) Glad my little input helped! Now try this... Here's some code (originally gotton from this group) that sends the active file as email Change the portions in quotes to suit your needs. steveb (Remove 'NOSPAM' from my email address if replying direct) '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''' Sub SendMyMail() Dim subj As String Application.DisplayAlerts = False If MsgBox("Ready to send?", vbYesNo + vbQuestion, " email") = vbYes Then Sheets(2).Select ActiveSheet.Copy ActiveSheet.Protect ActiveWorkbook.SaveAs "c:\Temp\filename.xls" subj = Cells(3, 2) & " WhatYouWant " subj = subj & InputBox("Add to your Subject Line", "email Subject") subj = WorksheetFunction.Proper(subj) 'Emails the activeworkbook' ActiveWorkbook.SendMail Recipients:="Your-email- address", _ Subject:=subj, ReturnReceipt:=True ActiveWorkbook.ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName ActiveWorkbook.Close False End If Application.DisplayAlerts = True End Sub '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''' '''''''''''''''''''''' "Michelle" wrote in message ... Thanks Steve B this worked great, however, I still have a problem... if you can help ... I then executed the macro but it did not pass the e-mail address so the command just hangs, here is the is the code for the macro, how do I pass the e-mail address and and send command to outlook via the excel macro? Sub SubmitSurvey() ' ' SubmitSurvey Macro ' Macro recorded 5/10/2004 ' ' Application.Dialogs(xlDialogSendMail).Show End Sub ----- steveb wrote: ----- Michelle, Tried this in a dummy workbook and changed the line: DoCmd.RunMacro stDocName to Run stDocName and it worked like a champ. (am using Excel 2002) hth steveb "Michelle" wrote in message ... Hi There, I could not get a commandbutton (that I titled submit survey) to send the survey to me, so what I did was record a macro and tried to execute the macro from the button, it mostly works (see code below) but I keep getting object required, but do not understand what it is looking for ... Is anybody out.... Can Anybody help me :{ ... Confused and frustrated... Private Sub CommandButton1_Click() On Error GoTo Err_Command1_Click Dim stDocName As String stDocName = "SubmitSurvey" DoCmd.RunMacro stDocName Exit_Command1_Click: Exit Sub Err_Command1_Click: MsgBox Err.Description Resume Exit_Command1_Click End Sub "Michelle" wrote in message ... Hi Jeremy, I saw your original message and thought it was great! This is something that I needed. However, I needed to send an attached file with that and tried a couple of options, as well as passing the command to outlook to automatically send the message so it is transparent to user once they have pressed the submit button (command button) How would I include in the below a file attachement (e.g. the document that I am in executing the code? ) Your help would be greatly appreciated. Michelle ----- Jeremy Gollehon wrote: ----- An error is raised if I try to send quotes to an email body throught code. I've worked around this by replacing the quotes with an apostrophe, but I was hoping someone out there knows how to pass the quotes through. Thanks to Ron Debruin for most of the code below. http://www.rondebruin.nl/sendmail.htm#selection -Jeremy --------------------------------------------------------------------- ---- Sub PrepareTheEmail() Dim sRecipient As String Dim sSubject As String Dim sMsg As String Dim sMail As String Const q As String = """" Const apos As String = "'" With ActiveCell sRecipient = .Offset(0, 8) sSubject = "Regarding: " & .Offset(0, 3) sMsg = .Offset(0, 7) & "," & vbNewLine & vbNewLine sMsg = sMsg & "Regarding:" & vbNewLine sMsg = sMsg & .Offset(0, 3) & vbNewLine & vbNewLine sMsg = sMsg & "Details: " & vbNewLine sMsg = sMsg & .Offset(0, 6) & vbNewLine & vbNewLine sMsg = sMsg & "Solution/Comments:" & vbNewLine sMsg = sMsg & .Offset(0, 9) & vbNewLine & vbNewLine sMsg = sMsg & .Offset(0, 10) End With 'Format message to work with Mail program by replacing 'spaces with %20, returns with %0D%0A, quotes with apostrophe With Application.WorksheetFunction sSubject = .Substitute(sSubject, " ", "%20") sMsg = .Substitute(sMsg, " ", "%20") sMsg = .Substitute(sMsg, vbNewLine, "%0D%0A") sMsg = .Substitute(sMsg, vbLf, "%0D%0A") sMsg = .Substitute(sMsg, q, apos) End With sMail = "mailto:" & sRecipient & _ "?subject=" & sSubject & _ "&body=" & sMsg ThisWorkbook.FollowHyperlink sMail End Sub --------------------------------------------------------------------- ---- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Code to create Email that includes quotes
Michelle,
That is the exact reason I always post any code I have with my questions. I'm happy you found some worth in my message and that steveb could help with your other problem. Does anyone out there have an answer to my original question? -Jeremy Michelle wrote: Hi Jeremy, I saw your original message and thought it was great! This is something that I needed. However, I needed to send an attached file with that and tried a couple of options, as well as passing the command to outlook to automatically send the message so it is transparent to user once they have pressed the submit button (command button) How would I include in the below a file attachement (e.g. the document that I am in executing the code? ) Your help would be greatly appreciated. Michelle |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Code to create Email that includes quotes
Michelle,
Glad this helped! (but the real credit goes to the group) I think the original code came from Ron: http://www.rondebruin.nl/sendmail.htm -- steveb (Remove 'NOSPAM' from email address if replying direct) "Michelle" wrote in message ... Jeremy and Steve B You both are awesome! It has been a long ... very long time, since I have done coding, and your help enabled me to be successful in accomplishing my goals, I was able to successfully modify the code provided by both of you. Your Assistance is greatly appreciated! Michelle |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Macro how to create email link for the email addresses in aRange or Selection | Excel Worksheet Functions | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
How do I create an email macro to auto fill the email? | Excel Discussion (Misc queries) | |||
Create a "recap" worksheet that includes all info from all worksh. | Excel Worksheet Functions | |||
Scope of variable includes all Form _and_ Code modules?? | Excel Programming |