Email records

[pauljhorak]

I am using Access '03. I have a very basic table and form set up to quote customers. I'd like to set up a command button on the form to email a specific record to a specific customer as the body of the Outlook message, not as an attachment. Is this possible? If not, any suggestions would be appreciated...

Paul

[Denburt]

I use CDONTS for this but the coding is indepth there may be other ways but I am a bit tired I will sleep on this and see if there may be a less complicated method.

[goldenbear]

I am using Access '03. I have a very basic table and form set up to quote customers. I'd like to set up a command button on the form to email a specific record to a specific customer as the body of the Outlook message, not as an attachment. Is this possible? If not, any suggestions would be appreciated...

Paul

Hi Paul,

I am looking to do the same thing. I want to send an email with several pieces of data from a specific record pasted into the body, not as an attachment. Did you ever get a response about how to do this simply?

GB

[Denburt]

My apologies at such a delay. Let me see if we can get you going. There are so many variables depending if you using exchange server etc. This is a VERY broad question, with not much information. Here is a script that you should be able to modify to help get you going. Mind this though the method I am showing uses HTML email so if they view the email in plain text it will look much different.

Dim strTo As String, strFrom As String
Dim stSub As String ' Subject
Dim stMsg As String ' Body of Message
Dim stRpt As String ' Name of Report
Dim strCC As String
Dim strBcc As String
Dim iMsg As New CDO.Message
Dim iconf As New CDO.Configuration
Dim Flds
Dim fso, ts, tsR, Cnt(3)

strTo = "[B]1@2.somemail.com[/B]"
strCC = "[B]Only if you want[/B]"
strBcc = "Alwys include myself I like to know it goes out." 'More or less using this as a backup since if it error out I get that email also.
strFrom = "[B]WhoEver[/B]"
stSub = "[B]...Orders Report...[/B]"

'Here I open the report and export it as HTML
DoCmd.OpenReport "[B]My Orders[/B]", acViewPreview
DoCmd.OutputTo acOutputReport, "[B]StupidReport[/B]", acFormatHTML, Application.CurrentProject.Path & "\[B]RD.HTML[/B]", False
Const ForReading = 1, ForWriting = 2

'Here I open the HTML and make any known needed changes. Exporting to HTMl always screws up lines etc.
 Set fso = CreateObject("Scripting.FileSystemObject")
 Set ts = fso.OpenTextFile(Application.CurrentProject.Path & "[B]\RD.HTML[/B]", ForReading, False) '.CreateTextFile(vFile)


tsR = ts.ReadAll
tsR = Replace(tsR, "&nbsp", "&nbsp")
tsR = Replace(tsR, "<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0 >" & vbCrLf & "<TR HEIGHT=18 >" & vbCrLf & "<TD WIDTH=687  ALIGN=LEFT > <BR></TD><TD WIDTH=97  ALIGN=CENTER ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>AEC</FONT></B></I></TD>", _
                    "<TABLE style=""border-bottom: solid"" CELLSPACING=0 CELLPADDING=0 >" & vbCrLf & "<TR HEIGHT=18 >" & vbCrLf & "<td Width=775 colspan=""7""></td><TD colspan=""2"" style=""border-top: solid;border-left: solid;;border-right: solid;"" WIDTH=67  ALIGN=CENTER ><B><I><FONT style=FONT-SIZE:11pt FACE=""Times New Roman"" COLOR=#000080>AEC</FONT></B></I></TD>")

Set ts = fso.OpenTextFile(Application.CurrentProject.Path & "\[B]RD.HTML[/B]", ForWriting, True)
 ts.write (tsR)
  ts.Close
 Set ts = Nothing

Const cdoSendUsingPort = 2
Const strSmartHost = "[B]EchangeServerI[/B]" 

Set iMsg = CreateObject("CDO.Message")
Set iconf = CreateObject("CDO.Configuration")

' Set the configuration fields.
Set Flds = iconf.Fields

' Set the proxy server to be used.
Flds.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort

' TODO: Set "someproxy:80" to the name of your proxy server.
'Flds("http://schemas.microsoft.com/cdo/configuration/urlproxyserver") = "SomeExchangeSeverAdddy"
Flds.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strSmartHost

' Set if this is a local server.
'Flds("http://schemas.microsoft.com/cdo/configuration/urlproxybypass") = "<local>"

' Set the option to retrieve the latest content directly from the server.
Flds("http://schemas.microsoft.com/cdo/configuration/urlgetlatestversion") = True
Flds.Update

' Set the message properties.
Set .Configuration = iconf
' Create the MIME representation of the Web page in the message.
' TODO: Change the To and From fields to valid e-mail addresses.
'.CreateMHTMLBody = "http//whatever.com"
.HTMLBody = tsR 
'.CreateMHTMLBody Application.CurrentProject.Path & "\[B]RD.HTML[/B]"
.To = strTo
.CC = strCC
.BCC = strBcc
.From = strFrom
.Subject = stSub
.Send

Set .Configuration = Nothing
Set Flds = Nothing
Set iconf = Nothing
Set iMsg = Nothing
Set fso = Nothing

End With

As you can see I have many commands commented out as I didn't have a need for them, there are many more commands that are available, and I tried to bold the items you should change to suite your needs.. Hopefully you can make some heads and or tails out of it. Good luck. Let us know.

[goldenbear]

Thanks for the input,

I can created the htm file and save it but have problems with reading and re-writing it and getting it to the body of an OUTLOOK email. I don't really car about the TO, FROM etc. just want to dump the htm into the body of an OUTLOOK email.

The first line of code "Dim iMsg As New CDO.Message" returns a compile error "user-type not defined."

Also, my Access db is on a MS 2003 server, could this be the problem with cdo?

I am running MS 2000 and IIS using CDONTS for sending email but I need to keep Access 2003 on the 2003 server and LAN.

[Denburt]

As a side not I used this once to send an email to my boss telling him he was fired... :)

strFrom = "WhoEver"

It looked just like it came from the higher ups but I made sure I was nearby when he opened it.

[slynch401k]

Here's some code I use. I set up a form with the fields below. Form name is "Group Email" Button on click event uses this code. note you may need to set a referend in VB to the outlook library.

Private Sub Command61_Click()

Dim EmailApp, NameSpace, EmailSend As Object
Dim MsgBody As String
Dim Attachment As String
Dim EmailAddress As String
Set EmailApp = CreateObject("Outlook.Application")
Set NameSpace = EmailApp.GetNamespace("MAPI")
Set EmailSend = EmailApp.CreateItem(0)


'Attachment = [Forms]![Group Email]![AttachmentPath]
EmailAddress = [Forms]![Group Email]![Text42]

MsgBody = "Dear " & Forms![Group Email]![Text62] & ":" & vbCrLf & vbCrLf & _
Forms![Group Email]![Body] & vbCrLf & vbCrLf & ""
    
EmailSend.to = EmailAddress ' Put email address here
EmailSend.Subject = [Forms]![Group Email]![Text71]
EmailSend.Body = MsgBody
'EmailSend.Attachments.Add Attachment ' Change this to match your path
'EmailSend.Attachments.Add "c:\filetoattach.txt" ' Change this to match your path
EmailSend.Display ' Remove this line if you don't want to see email

Set EmailApp = Nothing
Set NameSpace = Nothing
Set EmailSend = Nothing


End Sub

[NeoPa]

Thanks for the input,

I can created the htm file and save it but have problems with reading and re-writing it and getting it to the body of an OUTLOOK email. I don't really car about the TO, FROM etc. just want to dump the htm into the body of an OUTLOOK email.

The first line of code "Dim iMsg As New CDO.Message" returns a compile error "user-type not defined."

Also, my Access db is on a MS 2003 server, could this be the problem with cdo?

I am running MS 2000 and IIS using CDONTS for sending email but I need to keep Access 2003 on the 2003 server and LAN.

Goldenbear,

You're welcome to get whatever useful information you can from this thread. What you are not permitted to do is to divert this thread with your own questions or details.
You can ask for clarification, but diverting the thread is hijacking and is not allowed. If you need separate help with your problem you need to post your own question. Feel free to post a link to this thread if you feel that will be helpful.

MODERATOR.

PS. your error message probably comes from the library not being referenced.

[NeoPa]

@goldenbear
You can also have a special dispensation to post a link to your new question in here so that the current list of members already subscribed will know immediately that it is a similar question.

[goldenbear]

Here's some code I use. I set up a form with the fields below. Form name is "Group Email" Button on click event uses this code. note you may need to set a referend in VB to the outlook library.

Private Sub Command61_Click()

Dim EmailApp, NameSpace, EmailSend As Object
Dim MsgBody As String
Dim Attachment As String
Dim EmailAddress As String
Set EmailApp = CreateObject("Outlook.Application")
Set NameSpace = EmailApp.GetNamespace("MAPI")
Set EmailSend = EmailApp.CreateItem(0)


'Attachment = [Forms]![Group Email]![AttachmentPath]
EmailAddress = [Forms]![Group Email]![Text42]

MsgBody = "Dear " & Forms![Group Email]![Text62] & ":" & vbCrLf & vbCrLf & _
Forms![Group Email]![Body] & vbCrLf & vbCrLf & ""
    
EmailSend.to = EmailAddress ' Put email address here
EmailSend.Subject = [Forms]![Group Email]![Text71]
EmailSend.Body = MsgBody
'EmailSend.Attachments.Add Attachment ' Change this to match your path
'EmailSend.Attachments.Add "c:\filetoattach.txt" ' Change this to match your path
EmailSend.Display ' Remove this line if you don't want to see email

Set EmailApp = Nothing
Set NameSpace = Nothing
Set EmailSend = Nothing


End Sub

Thanks slynch401k,

This worrked perfectly!!

GB

[Denburt]

Glad you got it. I did leave out the VBA reference to Microsoft CDO for windows.

[NeoPa]

Den,

GoldenBear is NOT the OP (pauljhorak).
It's not a problem that he gets it, but let's not allow the thread hijack.

@GoldenBear,
You would be well advised in future to pay attention to moderators' posts - especially when they're directed at you.
You have now successfully diverted attention in this thread away from the OP's question to your own needs. This is not acceptable and will not continue (trust me on this one).
If you don't feel you can conform to the rules of this site then we'll just have to arrange that you are no longer in a position to break them.

MODERATOR.

[Denburt]

Your exactly right Neo.

Glad you got it GoldenBear.

Paul if your still with us has any of this helped you in any way?

Do you have any questions comments?

[goldenbear]

Your exactly right Neo.

Glad you got it GoldenBear.

Paul if your still with us has any of this helped you in any way?

Do you have any questions comments?

Denburt,

I thought my original questions/coomments were in line with the question Paul asked. I understood your comments when you first made them and had no intention of "diverting the thread" any further, once I understood the rules. Trust me it won't happen again! Don't forget I am a newbie.

Thanks again for the helpand have a great weekend.

[NeoPa]

Denburt,

I thought my original questions/coomments were in line with the question Paul asked. I understood your comments when you first made them and had no intention of "diverting the thread" any further, once I understood the rules. Trust me it won't happen again! Don't forget I am a newbie.

Thanks again for the helpand have a great weekend.

GoldenBear,
We do appreciate that you're new to the site and we're glad you understand things a little better now.
A response to either of my earlier posts would have let us know that you understood though, and further posts would probably have been felt unnecessary.
That all said, I hope you had a great weekend and we really do hope you come back and benefit more from the site. It's what it's here for ;)