Code to create Email that includes quotes

[Jeremy Gollehon]

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

 

[Michelle]

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

 

[steveb]

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
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''

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

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...

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? )

 

[Jeremy Gollehon]

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

 

[steveb]

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)

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.