Mail User Based on Loaded Form

Lochwood

Registered User.
Local time
Today, 13:40
Joined
Jun 7, 2017
Messages
130
Hi there,

I have created a helpdesk database that works the way it should. When I close the helpdesk call I would like the code behind the "Close Call" button to mail the actual user that logged the call. I have created the code that can mail to the address I type into the code but how do I get the code to mail the user that corresponds to the call I am closing.

I have the address of the user that logged the call as a hidden field in the form. see code below whick also includes a date and time stamp when I close the call.

Private Sub Command77_Click()
Me.[Date Closed] = Date
Me.[Time_Closed] = Time
DoCmd.Close
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
End If
'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)
With objMail
'Set body format to HTML
.BodyFormat = olFormatHTML
.To = "[me.helpdesk_engineer_fault_full].[mail address]"
.Subject = "Your Helpdesk Call Has Been Closed"
.HTMLBody = "Your helpdesk call has now been resolved. please login and rate your support experience"
.send
End With
MsgBox "Operation completed successfully"

End Sub
 
Code:
.To = Me.[mail address]
if that is the name of your hidden control on the form.

FWIW people recommend not to use spaces in control names and fields.

I would have used MailAddress, then it would just be

Code:
.To = Me.MailAddress

HTH
 
Hi thanks for the reply. I have tried this but still not working and I have also changed field to MailAddress to see if this was causing the issue. I don't get any errors in the code just no email

My code now reads

Private Sub Command77_Click()
Me.[Date Closed] = Date
Me.[Time_Closed] = Time
DoCmd.Close
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
End If
'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)
With objMail
'Set body format to HTML
.BodyFormat = olFormatHTML
.To = Me.MailAddress
.Subject = "Your Helpdesk Call Has Been Closed"
.HTMLBody = "Your helpdesk call has now been resolved. please login and rate your support experience"
.send
End With
MsgBox "Operation completed successfully"

End Sub
 
do you have Subform on your form?
what is [helpdesk_engineer_fault_full]?
 
do you have Subform on your form?
what is [helpdesk_engineer_fault_full]?

No subform this is the name of the actual main form I have also made the MaillAddress field visible and can see the correct mail address for each user being populated in the form.
 
No subform this is the name of the actual main form I have also made the MaillAddress field visible and can see the correct mail address for each user being populated in the form.

Walk through the code in the debugger.
It is going to be something silly.
 
instead of using (.To) replace it with:

Code:
Dim objOutlookRecip
Set objOutlookRecip = .Recipients.Add(Me.EmailAddress)
objOutlookRecip.type = olTo  'or 1
 
instead of using (.To) replace it with:

Code:
Dim objOutlookRecip
Set objOutlookRecip = .Recipients.Add(Me.EmailAddress)
objOutlookRecip.type = olTo  'or 1

Om I tried that see below but still don't seem to get an email.

Private Sub Command77_Click()
Me.[Date Closed] = Date
Me.[Time_Closed] = Time
DoCmd.Close
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
End If
'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)
With objMail
'Set body format to HTML
.BodyFormat = olFormatHTML
Dim objOutlookRecip
Set objOutlookRecip = .Recipients.Add(Me.EmailAddress)
objOutlookRecip.Type = olTo 'or 1
.Subject = "Your Helpdesk Call Has Been Closed"
.HTMLBody = "Your helpdesk call has now been resolved. please login and rate your support experience"
.send
End With
MsgBox "Operation completed successfully"

End Sub
 
comment (') out the .SEND in your code and instead use .Display.
outlook window will appear.
see if there is email address in outlook email:

Code:
'.Send
.Display
 
comment (') out the .SEND in your code and instead use .Display.
outlook window will appear.
see if there is email address in outlook email:

Code:
'.Send
.Display

No I don't get an email address in the to box. it is blank
 
No I don't get an email address in the to box. it is blank

That seems to indicate that your control is empty?
The debugger would confirm that?

I have the following in Excel and it works no problem
Code:
 With OutMail
        .Display
    End With
    With OutMail
        .to = pStrToNames
        .BCC = ""
        .Subject = pstrSubject
        .htmlbody = Replace(.htmlbody, strDiv, strDiv & strBody)
        '.attachments.Add pstrFilename & ".pdf"
        .attachments.Add pstrFilename & ".xlsx"
        .Send
    End With
 
That seems to indicate that your control is empty?
The debugger would confirm that?

I have the following in Excel and it works no problem
Code:
 With OutMail
        .Display
    End With
    With OutMail
        .to = pStrToNames
        .BCC = ""
        .Subject = pstrSubject
        .htmlbody = Replace(.htmlbody, strDiv, strDiv & strBody)
        '.attachments.Add pstrFilename & ".pdf"
        .attachments.Add pstrFilename & ".xlsx"
        .Send
    End With

How do I do that. all I have ever done is Compile in VB which all comes back fine
 
you use the name of the Textbox of your email address on the form and not the Field name.
or is it a textbox or a label?
if label you use lablename.Caption.
 
Ok I got it working by moving

Me.[Date Closed] = Date
Me.[Time_Closed] = Time
DoCmd.Close


to the end of the code. thinking the form was closing before the script got the chance to read field emailaddress.


Private Sub Command77_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
End If
'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)
With objMail
'Set body format to HTML
.BodyFormat = olFormatHTML
.To = Me.EmailAddress
.Subject = "Your Helpdesk Call Has Been Closed"
.HTMLBody = "Your helpdesk call has now been resolved. please login and rate your support experience"
.send
'.Display
End With
MsgBox "Operation completed successfully"
Me.[Date Closed] = Date
Me.[Time_Closed] = Time
DoCmd.Close


Thanks for all your help
 

Users who are viewing this thread

Back
Top Bottom