Sending email out of a form using data in the form (1 Viewer)

[mlozano]

Hi guys
I am using Access 2010

I have a form to register deliveries to customers

ui would like to be able to do the following

1.- Have Access open an Outlook message window
2.- Popuilate the To: field with the shipping agency's email address (can be different shipping agencies, in each shipment we choose the agency from an existing table which contains the email address of each one)
3.- populate the Subkect fiel with "Pickup Notice # [ShipmentNumber]" whee [ShipmentNumber] is a control on the form
4.- Populate de body of the message wit some text and values from different records, such as

Dear [ShipAgentContact]
Please arrange pickup opf shipent # [ShipmentNumber]
There are [ShippedParcelss] parcels to pick up.
The pickup adress is asfollows:
[ShipFromName]
[ShipFrom Address1]
[ShipFromAddress2]
[ShipFromPostcode[ & [ShipFromCity]
[ShipFromContactName]
[ShipFromContactPhone]

The delivery address is as follows

[ShipToName]
[ShipToAddress1]
[ShipToAddress2]
[ShipToPostcode[ & [ShipToCity]
[ShipToContactName]
[ShipToContactPhone]

Thanks and best regards

[Sendername]
[SenderPhone]



Is this possible? Can anyone provide code I can modigy to do it?- Currently I use SendObject and send a report in PDF format but it would be much better not to send any attachment and put the information in the message body instead.

Any help will be greatly appreciated

many thanks!

 

[Isskint]

The basic code is:

Dim olApp As Object
Dim objMail As Object

On Error Resume Next 'Keep going if there is an error


Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open


If Err Then 'Outlook is not open
Set olApp = CreateObject("Outlook.Application") 'Create a new instance of Outlook
End If

'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)

With objMail

    'Set body format to HTML
    .BodyFormat = olFormatHTML
    .To = ?
    .Subject = ?
    .HTMLBody = ?
    .Display

End With

You just need to add data from your record(s) in the relevant places

 

[mlozano]

Thanks a lot!
i have put it to work and it does open a message window!.
Further questions

How do I specify the table and field where it has to get the values from? I am totally clueless with VBA syntax...

For instance, the email address wouldn't be in the form, it would be in the table containing the info for the shipping agencies. Of course the form has a control to specify the shipping agency where the shipping agency's primary key is selected from a dropdown list.

Other than that

Can I directly use HTML directly after

.HTMLBody =

Thanks!

 

[Isskint]

Any values that are in your open form you can address as Forms!FormName.field - EG Forms!frmProgress.OpName would give you the value in the field OpName on a form called frmProgress.

Values contained in other tables, you can address using DLookup(), DCount(), DSum() etc - EG DLookup("[Address1]","[SomeTable]","[TheName]='" & Forms!frmProgress.OpName & "'") will return the value of the field Address1 in the table SomeTable WHERE TheName = the value held in Forms!frmProgress.OpName.

If you are using Combo boxes (Drop downs), include the field you want to reference in the combo box (just set the width to 0 so it does not display) and then you can use the Column() property to reference it. EG Forms!frmProgress.OpName.Column(1) would return the value in the SECOND column (column count starts at 0).

Yes you can use HTML. Just use the usual tags at the start and end of the message.

 

[mlozano]

Hi again
Thanks for all your help I can't believe Things are actually almost working!

I have a problem. The data which are retrieved directly from controls in the current form are displayer OK in the email.
However, the data which is taken from tables is wrong, for some reason the code does not searcgh the table for the relevan record, it displays the data from the first record of the table.

What follows is the code I am using

Private Sub Comando50_Click()
Dim olApp As Object
Dim objMail As Object

On Error Resume Next 'Keep going if there is an error


Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open


If Err Then 'Outlook is not open
Set olApp = CreateObject("Outlook.Application") 'Create a new instance of Outlook
End If

'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)


With objMail

'Set body format to HTML
.To = DLookup("", "[transportista]", "[idtransporte]='" & Forms!Albaranes.transporte & "'")
.Subject = "Recogida " & Forms!Albaranes.reftransporte
.Body = "Fecha de recogida: " & Forms!Albaranes.Fecha & ", Fecha de entrega: " & Forms!Albaranes.fentrega & vbNewLine & _
"Nº Referencia: " & Forms!Albaranes.reftransporte & ", Nº de bultos: " & Forms!Albaranes.bultos & vbNewLine & _
" " & vbNewLine & _
"Dirección de recogida" & vbNewLine & _
DLookup("[EMPRESA]", "[DIRRECOGIDA]", "[recogeren]='" & Me.recogeren & "'") & vbNewLine & _
DLookup("[DIRECCION1]", "[DIRRECOGIDA]", "[recogeren]='" & Me.recogeren & "'") & vbNewLine & _
DLookup("[DIRECCION2]", "[DIRRECOGIDA]", "[recogeren]='" & Me.recogeren & "'") & vbNewLine & _
DLookup("[CPOSTAL]", "[DIRRECOGIDA]", "[recogeren]='" & Me.recogeren & "'") & " " & DLookup("[POBLACION]", "[DIRRECOGIDA]", "[recogeren]='" & Forms!Albaranes.recogeren & "'") & " (" & DLookup("[PROVINCIA]", "[DIRRECOGIDA]", "[recogeren]='" & Me.recogeren & "'") & ")" & vbNewLine & _
"Persona de contacto: " & DLookup("[PERSONA]", "[DIRRECOGIDA]", "[recogeren]='" & Me.recogeren & "'") & vbNewLine & _
"Teléfono: " & DLookup("[TELEFONO]", "[DIRRECOGIDA]", "[recogeren]='" & Me.recogeren & "'") & vbNewLine & _
" " & vbNewLine & _
"Dirección de entrega:" & vbNewLine & _
DLookup("[nombreDireccion]", "[direccion]", "[direccion]='" & Me.direccion & "'") & vbNewLine & _
DLookup("[direccion1]", "[direccion]", "[direccion]='" & Me.direccion & "'") & vbNewLine & _
DLookup("[direccion2]", "[direccion]", "[direccion]='" & Me.direccion & "'") & vbNewLine & _
DLookup("[cpostalDireccion]", "[direccion]", "[direccion]='" & Me.direccion & "'") & " " & DLookup("[poblacionDireccion]", "[direccion]", "[direccion]='" & Forms!Albaranes.direccion & "'") & " (" & DLookup("[poblacionDireccion]", "[direccion]", "[direccion]='" & Me.direccion & "'") & ")" & vbNewLine & _
"Persona de Contacto: " & DLookup("[contactoDireccion]", "[direccion]", "[direccion]='" & Me.direccion & "'") & vbNewLine & _
"Teléfono: " & DLookup("[telefonoDireccion]", "[direccion]", "[direccion]='" & Me.direccion & "'") & vbNewLine & _
" " & vbNewLine & _
"Gracias y saludos"
.Display

End With
End Sub


What am I doing wrong?

Any help is GREATLY appreciated! Many thanks!