creating different emails dependant upon a control value

G

Guest

I want to be able to produce two different emails. one with just standard
financial info on (got this working) and one with additional variation info
on (not working)

i need someone to look at the code i have below and highlight where i have
gone wrong and what needs to be done.

i am using Access 2000 and Outlook 2002.

thanks in advance

Private Sub Form_Open(Cancel As Integer)
'Arvin Meyer 03/12/1999
'Updated 7/21/2001
'ammended by Peter Coxsey 28/06/2007



On Error GoTo Error_Handler

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
'Dim objEmailSub As String
Dim emlAtt As String
Dim emlrcp As String


If Me.Variation_Required = "No" Then

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)
emlrcp = Me.Build_Manager
'emlAtt = form!sub_FAsub_info_frm.Form!File_path

With objEmail
.To = emlrcp
.Subject = "OLO Application " & Me.OLO_REF & ", Site ID " & Me.Site_ID &
", " & Me.Payment_Type
.Body = "Please find attached the signed FA for the above referenced
site." & vbCrLf & _
vbCrLf & _
vbCrLf & _
"The acquisition PO can be receipted to the value of £" &
Me.Agreed_FA_Value_ACQ & vbCrLf & _
vbCrLf & _
"The Build PO can be receipted to the value of £" &
Me.Agreed_FA_Value_Build & vbCrLf & _
vbCrLf & _
Me.Payment_Description & _
vbCrLf & _
vbCrLf & _
vbCrLf & _
"Regards" & vbCrLf & _
vbCrLf & _
vbCrLf & _
"xxxxxxxxxxxx" & vbCrLf & _
"xxxxxxxxxxxxxxxx" & vbCrLf & _
"Mob: xxxxxxxxxxxxxx" & vbCrLf & _
"Email: xxxxxxxxxxxxxx"



'Attachments.Add emlAttachment
'.Attachments.Add "Me.FAsub_info_frm.Form.file_path"
'.Send
.ReadReceiptRequested = True
.Display
End With

Else

'If Me.Variation_Required = "Yes" Then


Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)
emlrcp = Me.Build_Manager
'emlAtt = form!sub_FAsub_info_frm.Form!File_path

With objEmail
.To = emlrcp
.Subject = "OLO Application " & Me.OLO_REF & ", Site ID " & Me.Site_ID &
", " & Me.Payment_Type
.Body = "Please find attached the signed FA for the above referenced
site." & vbCrLf & _
"Note that a variation is required to cover all agreed works." & vbCrLf
& _
vbCrLf & _
"The acquisition PO can be receipted to the value of £" &
Me.Acq_Variation & vbCrLf & _
vbCrLf & _
"The Build PO can be receipted to the value of £" & Me.Build_Variation &
vbCrLf & _
vbCrLf & _
"The variation should be raised under " & Me.To_Be_Raised_As & " to the
value of " & Me.Variation_Ammount & vbCrLf & _
"The variation can be receipted to the value of " &
Me.Receipt_Variation_Value & vbCrLf & _
Me.Payment_Description & _
vbCrLf & _
vbCrLf & _
vbCrLf & _
"Regards" & vbCrLf & _
vbCrLf & _
vbCrLf & _
"xxxxxxxxxxx" & vbCrLf & _
"xxxxxxxxxxxxxxxxxxxx" & vbCrLf & _
"Mob: xxxxxxxxxxxx" & vbCrLf & _
"Email: xxxxxxxxxxx"

'Attachments.Add emlAttachment
'.Attachments.Add "Me.FAsub_info_frm.Form.file_path"
'.Send
.ReadReceiptRequested = True
.Display
End With

End If
'End If

Exit_Here:
Set objOutlook = Nothing
Exit Sub

Error_Handler:
MsgBox Err & ": " & Err.Description
Resume Exit_Here

End Sub
 
D

Douglas J. Steele

Rather than repeat all that code just for a slightly different message, why
not put the message into a variable, and use the variable?

Private Sub Form_Open(Cancel As Integer)
'Arvin Meyer 03/12/1999
'Updated 7/21/2001
'ammended by Peter Coxsey 28/06/2007

On Error GoTo Error_Handler

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
'Dim objEmailSub As String
Dim emlAtt As String
Dim emlrcp As String
Dim strMessage As String

If Me.Variation_Required = "No" Then
strMessage = "Please find attached the signed FA " & _
"for the above referenced site." & vbCrLf & _
vbCrLf & _
vbCrLf & _
"The acquisition PO can be receipted to the value of £" & _
Me.Agreed_FA_Value_ACQ & vbCrLf & _
vbCrLf & _
"The Build PO can be receipted to the value of £" & _
Me.Agreed_FA_Value_Build & vbCrLf & _
vbCrLf & _
Me.Payment_Description & _
vbCrLf & _
vbCrLf & _
vbCrLf & _
"Regards" & vbCrLf & _
vbCrLf & _
vbCrLf & _
"xxxxxxxxxxxx" & vbCrLf & _
"xxxxxxxxxxxxxxxx" & vbCrLf & _
"Mob: xxxxxxxxxxxxxx" & vbCrLf & _
"Email: xxxxxxxxxxxxxx"
Else
strMessage = "Please find attached the signed FA " & _
"for the above referenced site." & vbCrLf & _
"Note that a variation is required to cover all agreed works." & vbCrLf
& _
vbCrLf & _
"The acquisition PO can be receipted to the value of £" & _
Me.Acq_Variation & vbCrLf & _
vbCrLf & _
"The Build PO can be receipted to the value of £" & _
Me.Build_Variation & vbCrLf & _
vbCrLf & _
"The variation should be raised under " & Me.To_Be_Raised_As & _
" to the value of " & Me.Variation_Ammount & vbCrLf & _
"The variation can be receipted to the value of " & _
Me.Receipt_Variation_Value & vbCrLf & _
Me.Payment_Description & _
vbCrLf & _
vbCrLf & _
vbCrLf & _
"Regards" & vbCrLf & _
vbCrLf & _
vbCrLf & _
"xxxxxxxxxxx" & vbCrLf & _
"xxxxxxxxxxxxxxxxxxxx" & vbCrLf & _
"Mob: xxxxxxxxxxxx" & vbCrLf & _
"Email: xxxxxxxxxxx"
End If

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)
emlrcp = Me.Build_Manager

With objEmail
.To = emlrcp
.Subject = "OLO Application " & Me.OLO_REF & ", Site ID " & _
Me.Site_ID & ", " & Me.Payment_Type
.Body = strMessage
.ReadReceiptRequested = True
.Display
End With

Exit_Here:
Set objOutlook = Nothing
Exit Sub

Error_Handler:
MsgBox Err & ": " & Err.Description
Resume Exit_Here

End Sub
 
G

Guest

Thanks Doug,

your suggestion partly worked i think the main error was using the on open
command as the fields hadn't calculated the vales prior to the code running.

do you know if you can delay the running of the code by 1 second whilst it
catches up with itself?

Thanks
 
G

Guest

Thanks Doug.
but i have been lazy and attached it to a command button.

my next problem tho is my two variables are not getting their value assigned
could you please look at the following code to see if you can see anything?

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
'Dim objEmailSub As String
Dim emlAtt As String
Dim emlrcp As String
Dim strMessage As String
Dim ACQovrP As Long
Dim BLDovrP As Long

If Me.Overpayment_Required = Yes Then

If Me.Agreed_FA_Value_ACQ > Me.Approved_AD_Costs Then
BLDovrP = Me.Agreed_FA_Value_Build + Me.Overpayment_Value
ACQovrP = Me.Approved_AD_Costs

ElseIf Me.Agreed_FA_Value_Build > Me.Approved_Build_Costs Then
ACQovrP = Me.Agreed_FA_Value_ACQ + Me.Overpayment_Value
BLDovrP = Me.Agreed_FA_Value_Build

End If
End If
DoEvents



Me.Acq_Overpayment_Value = ACQovrP
Me.Bld_Overpayment_Value = BLDovrP - Me.Retention
DoEvents
 
D

Douglas J. Steele

Sorry, I don't see what difference it makes that you've attached your code
to a command button.

Your code will not assign values to BLDovrP and ACQovrP if
Agreed_FA_Value_ACQ is less than or equal to Approved_AD_Costs and
Agreed_FA_Value_Build is less than or equal to Me.Approved_Build_Costs.
Might that be the problem you're encountering?
 
Top