email from Access to Outlook only changed records

[New2VB]

Greetings Gurus,

Before I drive myself nuts trying to find a way to do this I would like to ask if it can be done.

Mainform has a combobox - selects CompanyName
Subform has records - e.g. company, productcode, productprice, orderdate, supplydate

Currently I have a "Notify" button whose OnClick event sends an email to the relevant department so that if, for example, Orders ship an order an email will be received by Supply knowing that the order is en route.

It would be nice if, instead of alerting Supply (in the body of the email) that a company & product price has had a modification, we could add the company, productcode and the fields that have changed (sort of an OnDirty or On Data Change type event.

Is this possible?

 

[Trevor G]

Take a look at the code below as I use this all the time, getting people who want access to a database to email me.

You have to ensure that you have the reference set to MS Outlook.

You can adjust the contents of the body to the contents of your fields on a form. So hopefully this should help you achieve your goal.



Sub sendForApproval()
'*************************************************
'VBA Code created by Trevor G
'*************************************************
Dim olApp As Outlook.Application
Dim olMail As MailItem
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = "Email Address"
.Subject = "Access Request"
.Body = "Please can you provide access to the Database system." & vbCr & vbCr & _
"The required details are as follows my Full Name is: " & vbCr & _
"My User ID is: " & vbCr & _
"My Computer Name is: "
.Display

End With
Set olMail = Nothing
Set olApp = Nothing
End Sub

 

[New2VB]

Hi Trevor,

That's more or less what I have. I have an OnClick event which I would like to have do similar to this. (Combo4 - companyname)

Dim stChange As String
Dim NewValue as String
Dim NewOrder as String
Dim NewSupply as String

If [Forms]![xxxxxx]![xxxxxxSub]![ProductPrice] = _
[Forms]![xxxxxx]![xxxxxxSub]![ProductPrice].OldValue _
Then NewValue = Null(?)
Else NewValue = [Forms]![xxxxxx]![xxxxxxSub]![ProductPrice]
End If

If [Forms]![xxxxxx]![xxxxxxSub]![OrderDate] = _
[Forms]![xxxxxx]![xxxxxxSub]![OrderDate].OldValue _
Then NewOrder = Null(?)
Else NewOrder = [Forms]![xxxxxx]![xxxxxxSub]![OrderDate]
End If

If [Forms]![xxxxxx]![xxxxxxSub]![SupplyDate] = _
[Forms]![xxxxxx]![xxxxxxSub]![SupplyDate].OldValue _
Then NewSupply = Null(?)
Else NewSupply = [Forms]![xxxxxx]![xxxxxxSub]![SupplyDate]
End If

stChange = [Forms]![xxxxxx]!Combo4.Column(1) & Chr$(32) & [Forms]![xxxxxx]![xxxxxxSub]![ProductCode]

DoCmd.SendObject acSendNoObject, , , "xxxx@xxxxxx", , , ", (stChange) &
(NewValue) & (OrderDate) & (SupplyDate), False

I don't know how to put more than one entry in the Body field, sorry.

Do you think this is do-able?

As an aside, which method did you use to receive emails into your Access db?

 

[Trevor G]

Can you create a query on what has been updated to get a complete result? As you could then send the result into Excel or Word and use either of them to send the object as the body of the email.

 

[New2VB]

Hi Trevor,

I would rather not start pulling in 3rd party apps.

The way I am doing it now by using the Onclick to call a form which has checkboxes where the user chooses which category to notify of does work well, I was just looking for a more efficient way of doing it.

Is the code I posted not do-able (with rewrite of course).

Many thanks.