Send e-mail from access databases 65

[tpowers]

Now I am a new guy on the block, so bear with me. I have a form that users are entering client information into all day, and I have been asked to set the database up so that when the are done entering the record that it will automaticlly send the client a confirmation e-mail that the record as been recieved and entered into our database.

Again I am new at this so if so one as a clue of how to do this that would be great.

 

[nat1967]

Hi,

Its very easy to setup. However, you didnt leave us much information about your database. Are you using Outlook to send email? are you using a command button to submit?

Here is an example of how to send an email from a database. This example takes information directly from the form and uses it to build the email. It sends the email via Outlook. This is used behind a command button but you should be able to use it behind another Event procedure.

'******begin code******
Dim email, ref, origin, destination, notes As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'**gathers information from your form. this sets the string variable to your fields
email = Me!email
ref = Me!ref
origin = Me!origin
destination = Me!destination
notes = Me!notes

'***creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application&quot
Set objEmail = objOutlook.CreateItem(olMailItem)

'***creates and sends email
With objEmail
.To = email
.Subject = ref & " " & origin & " " & destination
.Body = notes
.Send
End With

'**closes outlook
Set objEmail = Nothing
objOutlook.Quit

Exit Sub
'****end code****

Hope that helps. Have a great day!!!! Please give helpful posts the Stars they deserve. It makes the post more visible for others

Nathan
Senior Test Lead

 

[nat1967]

Hi,

Just some more in case you are not using Outlook. This method uses SendObject.

'****begin code****

Private Sub Command10_Click()
Dim email, ref, origin, destination, notes As String

'***set references from your form
email = Me!email
ref = Me!ref
origin = Me!origin
destination = Me!destination
notes = Me!notes

'***set up the transaction that will format and send email
'***True at the end of the statement allows the email to be
'***edited before sent. change to False, and the email is
'***sent without edit
DoCmd.SendObject acSendForm, , acFormatTXT, email, , , ref & " " & origin & " " & destination, notes, True

End Sub

'****end code****

Hope this helps. Have a great day!!!! Please give helpful posts the Stars they deserve. It makes the post more visible for others

Nathan
Senior Test Lead

 

[tpowers]

nathan1967 thank you for your help, I think this will help. One question, I am using outlook, and I was wondering if I was using the following fields how would the command look then.


On the form I have the following fields:

Email

Scheduled Date

todays date

First name

Last name

Phone number

Address

City

State

Country

Zip

Account number

License plate number

issuing address

issue code

issue code description

and Special notes

These are all fields that need to be included in my email.
since I am new to this do you think you could show me where these fields would go in this command. I will most likely have a command button that will send the email.

again thank you for your time and help.

 

[nat1967]

Hi,

I have taken your fields and created a form letter. I hope this helps. I have tried it here and it works. Good Luck!

'******begin code*****
Private Sub cmdSendEmail_Click()
Dim strEmail, strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'**creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application&quot
Set objEmail = objOutlook.CreateItem(olMailItem)

'**************************************************************
'*create string with email address

strEmail = txtEmail

strBody = txtTDate & Chr(13) & Chr(13)
strBody = strBody & "Dear " & txtFName & " " & txtLName & "," & Chr(13) & Chr(13)
strBody = strBody & "We have received your information and are processing it promptly. Please review the information" & _
" below to ensure our accuracy." & Chr(13) & Chr(13) & Chr(13)
strBody = strBody & "Name: " & txtFName & " " & txtLName & Chr(13)
strBody = strBody & "Address: " & txtAddress & Chr(13)
strBody = strBody & "City, State, Zip: " & txtCity & ", " & txtState & ". " & txtZip & Chr(13)
strBody = strBody & "Country: " & txtCountry & Chr(13) & Chr(13)
strBody = strBody & "Account Number: " & txtAccount & Chr(13)
strBody = strBody & "Schedule Date: " & txtSDate & Chr(13)
strBody = strBody & "License: " & txtLicense & Chr(13)
strBody = strBody & "Issuing Address: " & txtIssueAddress & Chr(13)
strBody = strBody & "Issue Code: " & txtIssueCode & Chr(13)
strBody = strBody & "Issue Code Description: " & txtIssueCodeDescrip & Chr(13) & Chr(13)
strBody = strBody & "Special Notes: " & txtNotes & Chr(13) & Chr(13)
strBody = strBody & "Sincerely," & Chr(13) & Chr(13)
strBody = strBody & "Acme Corporation"

'***creates and sends email
With objEmail
.To = strEmail
.Subject = "Your information has been received"
.Body = strBody
.Send
End With


Set objEmail = Nothing
'****closes Outlook. remove if you do not want to close Outlook
objOutlook.Quit

Exit Sub

End Sub
'****end code*****

Have a Great Day!!! Please give helpful posts the Stars they deserve. It makes the post more visible for others

Nathan
Senior Test Lead

 

[tpowers]

Hey nathan1967, I got the code that you gave and I tried to run it however at run time I get this error "Compile error"
"User-defined type not defined" Once I click ok the debugger highlights this section of the code "Dim objOutlook As Outlook.Application" in blue and this section of the code "Private Sub Command34_Click()" in yellow, do you know why?

Thanks again for all your help.

 

[nat1967]

HI,

Sounds like you need to set a reference to Outlook. In the VB window, GoTo: Tools>References

Scroll through and add the reference for Microsoft Outlook. That should take care of it.

Have a great day!!!! Please give helpful posts the Stars they deserve. It makes the post more visible for others

Nathan
Senior Test Lead

 

[tpowers]

Everything works great. One question some times when I submit the email my outlook closes or my access database closes, is there a reason why?

 

[nat1967]

Hi,

Hmmm.... the Outlook I understand but I am not sure why the database would be closing. There is not anything in my code that closes Access. If your database is closing, post your complete code that is executing behind your command button. Maybe I can see something in there.

The Outlook closing is another story. There is a line in the code that closes Outlook after the email is sent.

'****begin code snippet***

Set objEmail = Nothing
'****closes Outlook. remove if you do not want to close Outlook
objOutlook.Quit

'****end code snippet*****

objOutlook.Quit will exit Outlook. If you dont want that to happen, comment out that line with a ' or remove completely.

That should take care of the Outlook closing. Let me know about the Access closing problem. I am scratching my head on that one.

Have a great day!!!! Please give helpful posts the Stars they deserve. It makes the post more visible for others

Nathan
Senior Test Lead

 

[cavery]

Hi Nathan,
Can you explain how this process can be done in Lotus notes?

Thanks,
Clark
Honda of America, Manufact.,

 

[nat1967]

Clark,

I wish I could answer that one. I havent worked in Lotus Notes.

I did find this fact.

thread702-92097

HTH

Have a Great Day!! Please give helpful posts the Stars they deserve. It makes the post more visible for others

Nathan
Senior Test Lead

 

[tpowers]

Hello nathan1967, I was wondering if you could help me with one more problem that I am having. what I did was I added on to the code that you gave me so that the form would cc some one as well, however now the form will not work if I do not include a cc email address, is there a way that I can tell it to only look for the cc control if the cc control contains an email address.

Thank you for all your help.

 

[nat1967]

Hi tpowers!!

I am glad to know things are going well for you.

On to your question.

Yes, I think this can be done. Hmmm....

I assume the following is how you formated your code:

'****begin****

'***creates and sends email
With objEmail
.To = strEmail
.CC = (your CC email address field or string)
.Subject = "Your information has been received"
.Body = strBody
.Send
End With
'****end****

I am pretty sure that would cause a problem. However, try something like this:

'****begin****

'***creates and sends email
With objEmail
.To = strEmail
If Not IsNull(your CC email field name) then
.CC = (your CC email field or string)
end if
.Subject = "Your information has been received"
.Body = strBody
.Send
End With
'****end *****

I think this will be what you need.

HTH


Please give helpful posts the Stars they deserve. It makes the post more visible for others

Nathan
Senior Test Lead

 

[tpowers]

nathan1967, what can I say. YOU ARE ON FIRE!!!!

Thank you so very very much for your help.

 

[nat1967]

You are very welcome!!

Helping people also helps me! I learn a lot this way.

Have a great day!!! Please give helpful posts the Stars they deserve. It makes the post more visible for others

Nathan
Senior Test Lead

 

[tpowers]

I have a question about something else that does not have to do with this topic its about passing data from one form to another do you think you could help me?

 

[POKY]

Hi nathan1967

u r realy GREAT!!!

1. can i send the form fields to execl the same way u did here????
2. what about sending record after record to other row each time i move in form ??

thanks
poky

 

[nat1967]

Hi Poky,

I have not done it but... I have been reading about and I need to do it soon.

Here is an example that someone gave to me. I have not a chance to look at it but I will pass it on to you. The intent is to copy the first "X" # of rows from the access table to the excel workbook.

'****begin*****

Sub sCopyRSExample()
'Copy records to first 20000 rows
'in an existing Excel Workbook and worksheet
'
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intLastCol As Integer
Const conMAX_ROWS = 20000
Const conSHT_NAME = "Feature"
Const conWKB_NAME = "c:\my documents\Microsoft Access\testing.xls"
Set db = CurrentDb
Set objXL = New Excel.Application
Set rs = db.OpenRecordset("Customers", dbOpenSnapshot)
With objXL
.Visible = True
Set objWkb = .Workbooks.Open(conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not err.number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME
End If
err.Clear
On Error GoTo 0
intLastCol = objSht.UsedRange.Columns.Count
With objSht
.Range(.Cells(1, 1), .Cells(conMAX_ROWS, _
intLastCol)).ClearContents
.Range(.Cells(1, 1), _
.Cells(1, rs.Fields.Count)).Font.Bold = True
.Range("A2&quot.CopyFromRecordset rs
End With
End With
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing
End Sub

'*****end *****

Also, you might check into the TransferSpreadsheet method in the help files. I have been looking at that also. You can use a query or table. If your form is based on one of these, it might work for you also.

Sorry I couldnt be more specific but hopefully this will be helpful.

Please give helpful posts the Stars they deserve. It makes the post more visible for others

Nathan
Senior Test Lead

 

[nat1967]

tpowers,

Lets give it a shot. I cant guarantee but I would like to at least try.

Please give helpful posts the Stars they deserve. It makes the post more visible for others

Nathan
Senior Test Lead

 

[SBendBuckeye]

Nathan,

Outstanding work! It was a pleasure to add to your star collection for this one. Perhaps you could turn all of that information into an FAQ.

Since this seems to have turned into an email thread, can someone give me some pointers on dealing with Attachments? How do you add them, can you open them outside of Outlook or Save them automatically from Outlook, etc.

Any help would be greatly appreciated.

Have a great day! Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!