Solved: Email Record

[hatched850]

I have a Publications request form and when a user completes it I want them to be able to me to fill the order. I see how to do it for a report but how would I do it from the completed form?:banghead:

Thanks in advance you guys are the best at helping us newbies.

[hatched850]

I want them to Email the order to me. Sorry I left that word out.

[CFDM]

:hi: :beerchug:

Private Sub cmdAssign_Click()
Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim cmd1 As ADODB.Command
Dim strSQL As String
Dim strRRDD As String
Dim fDlg As Office.FileDialog
Dim varFile As Variant
Dim strFolderName As String
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim strMailTo As String

'Create a file dialog box that will allow the user to select the folder
'that the files are exported to.
Set fDlg = Application.FileDialog(msoFileDialogFolderPicker)

With fDlg
.AllowMultiSelect = False
.Title = "Select folder to save export to."
.Filters.Clear

If .Show = True Then
For Each varFile In .SelectedItems
strFolderName = varFile
Next
End If
End With

'Export the files to the selected folder.
'retrieves the RRDD entered into the input box and specifies the date to
'create unique files

'set tblTrackingNumbers with strFileName
rst1.Open "tblRRDD", cnn1, adOpenDynamic, adLockOptimistic
rst1.MoveFirst
strRRDD = rst1("RRDD")
rst1.Close
strPath = strFolderName & "\" & strRRDD & CStr(Month(Date)) & CStr(Day(Date)) & _
CStr(Year(Date)) & "tblTrackingNumbers.txt"
DoCmd.TransferText acExportDelim, "ExporttblTrackingNumbers Export Specification", _
"ExporttblTrackingNumbers", strPath, -1

'sets tblCheckDepositDetail with strFileName
strPath = strFolderName & "\" & strRRDD & CStr(Month(Date)) & CStr(Day(Date)) & _
CStr(Year(Date)) & "tblCheckDepositDetail.txt"
DoCmd.TransferText acExportDelim, "ExporttblCheckDepositDetail Export Specification", _
"ExporttblCheckDepositDetail", strPath, -1

'sets tblDisbursements with strFileName
strPath = strFolderName & "\" & strRRDD & CStr(Month(Date)) & CStr(Day(Date)) & _
CStr(Year(Date)) & "tblDisbursement.txt"
DoCmd.TransferText acExportDelim, "ExporttblDisbursement Export Specification", _
"ExporttblDisbursement", strPath, -1

'Get the mail to address.
rst1.Open "tblMailTo", cnn1, adOpenForwardOnly, adLockOptimistic
rst1.MoveFirst
strMailTo = rst1("MailTo")
rst1.Close
Set rst1 = Nothing

'Create an email, attach the exported files and send the email.
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
Set objOutlookRecip = .Recipients.Add(strMailTo)

objOutlookRecip.Type = olTo

.Subject = strRRDD & " " & "Cashbook Deposit "
.Body = " " & vbCrLf & vbCrLf
.Importance = olImportanceHigh

Set objOutlookAttach = .Attachments.Add(strFolderName & "\" & strRRDD & _
CStr(Month(Date)) & CStr(Day(Date)) & _
CStr(Year(Date)) & "tblTrackingNumbers.txt")
Set objOutlookAttach = .Attachments.Add(strFolderName & "\" & strRRDD & _
CStr(Month(Date)) & CStr(Day(Date)) & _
CStr(Year(Date)) & "tblCheckDepositDetail.txt")
Set objOutlookAttach = .Attachments.Add(strFolderName & "\" & strRRDD & _
CStr(Month(Date)) & CStr(Day(Date)) & _
CStr(Year(Date)) & "tblDisbursement.txt")

For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve

If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next

.Send
End With

Set objOutlookMsg = Nothing
Set objOutlook = Nothing

DoCmd.Hourglass False
Me.lblMessage.Caption = "Process complete " & Chr(13) & Chr(10) & "CLOSING WINDOW "
Me.lblMessage.Visible = True
Wait 3
DoCmd.Close
End If

Forms!frmMainMenu.SetTrackingEmail
End Sub


Edited 24-Jun-06 by geekgirlau. Reason: insert vba tags

[OBP]

So are you going to send them a "blank" form for them to fill in?

[CFDM]

There is a form where the command button calls this code

[OBP]

You can also send an Access form in Word Rich Text Format which is fillable and returnable with this code


Dim subject As String, Body As String
subject = [Text0]
Body = [Text1] & Chr$(13) & [Text2] & Chr$(13) & [Text3] & Chr$(13) & [Text4]


DoCmd.SendObject acSendForm, "Send an email", acFormatRTF, "email address goes here", _
, , subject, Body, False


where the name of this particular form is "send an email".

Edited 24-Jun-06 by geekgirlau. Reason: insert vba tags

[hatched850]

I am a newbie and I was totally lost with that code most of my programming has been super simple. I have attached the Database I just want to have the database send me the publication request. So that I can either fill it with what I have in stock or order it. I don't necessarily need it to email me the form I just need it to notify me there is a request to be filled. I want them to fill out the form and then the database send me the request or email me that a request has been entered. Do you understand?

[OBP]

Hatched the code that I posted with a minor modification will do that.
I will look at your database, which form do you want the "Email Order" button on?

[hatched850]

The publication requests form

[OBP]

Ok, the attached version of your database has a new "Email Order" button on the form. I have tested it and it sends an email to me with the first few fields from the form in the email.
To send the email to you, you will have to change the email address in the VBA of the button.
Can you do that ?

[hatched850]

The code worked beautifully. It is doing just what I need it to. I don't know how to thank you. :beerchug: :bigdance2
Debbie

[OBP]

Debbie, that is great, can you marked this thread as solved using the thread tools at the top of the page please?