Mail User Based on Loaded Form (1 Viewer)

L

Lochwood

Registered User.
Local time
Today, 11:27
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
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 19:27
Joined
Sep 21, 2011
Messages
14,231
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
 
L

Lochwood

Registered User.
Local time
Today, 11:27
Joined
Jun 7, 2017
Messages
130
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
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:27
Joined
May 7, 2009
Messages
19,231
do you have Subform on your form?
what is [helpdesk_engineer_fault_full]?
 
L

Lochwood

Registered User.
Local time
Today, 11:27
Joined
Jun 7, 2017
Messages
130
arnelgp said:
do you have Subform on your form?
what is [helpdesk_engineer_fault_full]?
Click to expand...

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.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 19:27
Joined
Sep 21, 2011
Messages
14,231
Lochwood said:
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.
Click to expand...

Walk through the code in the debugger.
It is going to be something silly.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:27
Joined
May 7, 2009
Messages
19,231
instead of using (.To) replace it with:

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

Lochwood

Registered User.
Local time
Today, 11:27
Joined
Jun 7, 2017
Messages
130
arnelgp said:
instead of using (.To) replace it with:

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

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
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:27
Joined
May 7, 2009
Messages
19,231
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
 
L

Lochwood

Registered User.
Local time
Today, 11:27
Joined
Jun 7, 2017
Messages
130
arnelgp said:
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
Click to expand...

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

Gasman

Enthusiastic Amateur
Local time
Today, 19:27
Joined
Sep 21, 2011
Messages
14,231
Lochwood said:
No I don't get an email address in the to box. it is blank
Click to expand...

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
 
L

Lochwood

Registered User.
Local time
Today, 11:27
Joined
Jun 7, 2017
Messages
130
Gasman said:
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
Click to expand...

How do I do that. all I have ever done is Compile in VB which all comes back fine
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:27
Joined
May 7, 2009
Messages
19,231
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.
 
L

Lochwood

Registered User.
Local time
Today, 11:27
Joined
Jun 7, 2017
Messages
130
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
 
You must log in or register to reply here.

Similar threads

K
Solved How to add a signature to a mail
Replies
6
Views
419
KitaYama
K
L
Runtime Error 2147467259 (80004005)
Replies
11
Views
517
The_Doc_Man
The_Doc_Man
P
Loop gets limited
2
Replies
25
Views
842
Pat Hartman
P
C
Getting "The error 3061, Too few paramaters. Expected 6"
2
Replies
29
Views
1,312
Gasman
Gasman
M
E-mail hyperlinks not working from a table ...
Replies
6
Views
346
Marshall Brooks
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom