In Form a command button to send email reports

[bezforum]

I would like to develop a command on a form that uses the data in the current form and an additional relation table to generate a standard letter (whose text depends on the content of the form) which is displayed for verification and than sent through outlook to an email that is displayed in the form.

How can I do it?

[Allan Murphy]

Here is some code that I use to send the data on the current screen to a word document and then sends the document as an attachment.

Steps

1. I created a master document called form_letter_master.doc as more then one user maybe using the database to send a form letter. This master was stored in a central location for all users to access.

2. I created bookmarks in form_letter_master.doc to store the fields from the database in the relevant location of the document.

3. On my form I have a button called Completed that the user clicks to send the email using the similar code as shown below.

In the coding below change d:\projects\test_form_master.doc to the name and location of your form letter master copy

Change d:\projects\test_form.doc to the name and location of your form letter. If more than one user using this database then look at putting d:\projects\test_form.doc in their own personal folder.

' ************************************************** ****************
' send the data to a word document then email to Client
' ************************************************** *****************

Private Sub send_word_email()
On Error GoTo Err_send_word_email

Dim obj_word As Word.Application
Dim app_name As String
Dim suspect_type As String

' delete previous copy. This copy was used for emailing the previous form letter.
Kill ("d:\projects\test_form.doc")

'make a copy of the form letter
FileCopy ("d:\projects\test_form_master.doc"), ("d:\projects\test_form.doc")

' start microsoft word 2003
Set obj_word = CreateObject("Word.Application")

With obj_word
' make application visible
.Visible = True

'open the document
.Documents.Open ("d:\projects\test_form.doc")

' move to each bookmark and insert text from the form
' this populates the last name in the form letter with the last name on the screen
.ActiveDocument.Bookmarks("last_name").Select
.Selection.text = Me!last_name

'now continue with the remaining fields

End With

'close and save changes
obj_word.ActiveDocument.Close savechanges:=wdSaveChanges
obj_word.Quit
Set obj_word = Nothing

' open outlook and with the alert form as an attachment
app_name = "outlook.exe " & "d:\projects\test_form.doc"
Call Shell(app_name, 1)

Exit_send_word_email:
Exit Sub

Err_send_word_email:
MsgBox Err.description
Resume Exit_send_word_email
End Sub

[bezforum]

Here is some code that I use to send the data on the current screen to a word document and then sends the document as an attachment.

Steps

1. I created a master document called form_letter_master.doc as more then one user maybe using the database to send a form letter. This master was stored in a central location for all users to access.

2. I created bookmarks in form_letter_master.doc to store the fields from the database in the relevant location of the document.

3. On my form I have a button called Completed that the user clicks to send the email using the similar code as shown below.

In the coding below change d:\projects\test_form_master.doc to the name and location of your form letter master copy

Change d:\projects\test_form.doc to the name and location of your form letter. If more than one user using this database then look at putting d:\projects\test_form.doc in their own personal folder.

' ************************************************** ****************
' send the data to a word document then email to Client
' ************************************************** *****************

Private Sub send_word_email()
On Error GoTo Err_send_word_email

Dim obj_word As Word.Application
Dim app_name As String
Dim suspect_type As String

' delete previous copy. This copy was used for emailing the previous form letter.
Kill ("d:\projects\test_form.doc")

'make a copy of the form letter
FileCopy ("d:\projects\test_form_master.doc"), ("d:\projects\test_form.doc")

' start microsoft word 2003
Set obj_word = CreateObject("Word.Application")

With obj_word
' make application visible
.Visible = True

'open the document
.Documents.Open ("d:\projects\test_form.doc")

' move to each bookmark and insert text from the form
' this populates the last name in the form letter with the last name on the screen
.ActiveDocument.Bookmarks("last_name").Select
.Selection.text = Me!last_name

'now continue with the remaining fields

End With

'close and save changes
obj_word.ActiveDocument.Close savechanges:=wdSaveChanges
obj_word.Quit
Set obj_word = Nothing

' open outlook and with the alert form as an attachment
app_name = "outlook.exe " & "d:\projects\test_form.doc"
Call Shell(app_name, 1)

Exit_send_word_email:
Exit Sub

Err_send_word_email:
MsgBox Err.description
Resume Exit_send_word_email
End Sub

Allen, Thank you for your help.

Let me be more specific:
1. I am using access 2007.
2. The user opens a form, inspects some data displayed in the form and based on the data decides if to press the command button.
3. When the command button is pressed the following things should happen:
a. An email message is created which is based on a code which appears in the record corresponding to the data in the form. THE TEXT OF THE LETTER DEPENDS on that code, (what I did is to define a relation that consists of the code and a memo field which contains the text of the corresponding letter). The text contains placements for fields that will be inserted from the corresponding record or from fields in the form.
b. Once the letter is generated it is inserted as an email message (not as an attachement), the email address is taken from a field in the corresponding form/record. It is displayed to the user, who approves it and sends it out.
c. Based on the letter code a comment is added to a memo field in the corresponding record, with the date and time marked in it, the comment is appended as a new line in the memo field.
d. The reminder code field in the record is updated with the next code, so next time that a letter is generated, it will be based on the new code.
e. The command button becomes inactive until another record is displayed in the form.

[crisostomofred]

You kept referrring to a "code". What is this code? Allan's post is a typical way of doing what you want with the exception of not attaching a document. Explain or describe this code you are referring to and may we can help.