Function instead of Sub?

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

Hi

I am using Outlook Automation to send emails and assign tasks in a help desk
database I am, designing . I did have them as subs in my DB, but as they
started to grow, I decided to break them out and put them into a module as
functions. However, now I have done this nothing works! I click my command
button and nothing happens - no errors, no outlook.

Below is my code for sending a confirmation email via Outlook (both the sub
for calling the function and the function itself which resides in a module).

Can anyone suggest a reason why it would have stopped working? I considered
the possibility that these did not port over to functions well but I don't
know so I thought I'd ask the experts.

---

Private Sub cmdSendEmail_Click()

On Error GoTo Err_cmdSendEmail_Click

Me!HLP_Email.Value = Forms.SYS_Hardware!HDW_Email.Value

Call fncHelpDeskSendEmailOutlook

Exit_cmdSendEmail_Click:
Exit Sub

Err_cmdSendEmail_Click:
'MsgBox Err.Description
Resume Exit_cmdSendEmail_Click

End Sub

---

Function fncHelpDeskSendEmailOutlook()

Dim strEmail As String
Dim strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'**creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

'***email address to send to
strEmail = Forms![Sys_HelpDesk Subform].[HLP_Email]

'***main body of email
strBody = strBody & "Dear " & Forms![Sys_HelpDesk Subform].[HLP_ReportedBy]
& Chr(13) & Chr(13)

strBody = strBody & "Your IT Support issue has been logged on " &
Forms![Sys_HelpDesk Subform].[HLP_DateReported] & " at " &
Forms![Sys_HelpDesk Subform].[HLP_TimeReported] & _
", and has been assigned Call Number " & Forms![Sys_HelpDesk
Subform].[HLP_CallNumber] & "." & Chr(13) & Chr(13)

strBody = strBody & "Regards" & Chr(13) & Chr(13)
strBody = strBody & "Keith" & Chr(13)

'***creates and sends email

With objEmail
.To = strEmail
.Subject = "IT Support Request - Confirmation"
.Body = strBody
.Send
End With
'****end *****

Set objEmail = Nothing
'****closes Outlook
'objOutlook.Quit

End Function
 
The main difference between a function and a subroutine is
a function returns a value. That, and "Call" is not
required... ever.

For example.

Function SayHi() as String
SayHi = "Hi!"
End Function

You could use this in other code like this:

MsgBox SayHi

And you would get a messagebox that said, "Hi!".

A subroutine would implement it this way:

Sub SayHi()
Msgbox "Hi!"
End Sub

And you would effect it in code like this:

SayHi
(or) Call SayHi
(but don't use Call)

I hope this helps.

David Atkins, MCP
 
I loaded your code and after setting up references to include MS Outlook Object Library it worked just fine. However, the
email had not been sent, it was just put in the outbox and I could not find a method to send the emails. Was that what you're
missing?

On another note, the function you have isn't really a function, just a sub declared as a function. Functions return values,
subs don't. Either can be placed in a form module or a standard module, it makes little difference except with the calling
procedures. The only good reason to put it in a standard module would be for expanding use to multiple forms or not form
related at all. It looks like it still belongs in the form module.

If all else fails, just go with what you had before. If size is an issue in viewing and maintaining your code then just put a
few extra line breaks before and after the sub to distinguish it from the other code.

Hi

I am using Outlook Automation to send emails and assign tasks in a help desk
database I am, designing . I did have them as subs in my DB, but as they
started to grow, I decided to break them out and put them into a module as
functions. However, now I have done this nothing works! I click my command
button and nothing happens - no errors, no outlook.

Below is my code for sending a confirmation email via Outlook (both the sub
for calling the function and the function itself which resides in a module).

Can anyone suggest a reason why it would have stopped working? I considered
the possibility that these did not port over to functions well but I don't
know so I thought I'd ask the experts.

---

Private Sub cmdSendEmail_Click()

On Error GoTo Err_cmdSendEmail_Click

Me!HLP_Email.Value = Forms.SYS_Hardware!HDW_Email.Value

Call fncHelpDeskSendEmailOutlook

Exit_cmdSendEmail_Click:
Exit Sub

Err_cmdSendEmail_Click:
'MsgBox Err.Description
Resume Exit_cmdSendEmail_Click

End Sub

---

Function fncHelpDeskSendEmailOutlook()

Dim strEmail As String
Dim strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'**creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

'***email address to send to
strEmail = Forms![Sys_HelpDesk Subform].[HLP_Email]

'***main body of email
strBody = strBody & "Dear " & Forms![Sys_HelpDesk Subform].[HLP_ReportedBy]
& Chr(13) & Chr(13)

strBody = strBody & "Your IT Support issue has been logged on " &
Forms![Sys_HelpDesk Subform].[HLP_DateReported] & " at " &
Forms![Sys_HelpDesk Subform].[HLP_TimeReported] & _
", and has been assigned Call Number " & Forms![Sys_HelpDesk
Subform].[HLP_CallNumber] & "." & Chr(13) & Chr(13)

strBody = strBody & "Regards" & Chr(13) & Chr(13)
strBody = strBody & "Keith" & Chr(13)

'***creates and sends email

With objEmail
.To = strEmail
.Subject = "IT Support Request - Confirmation"
.Body = strBody
.Send
End With
'****end *****

Set objEmail = Nothing
'****closes Outlook
'objOutlook.Quit

End Function
Click to expand...

Jeremiah Ellison
Ellison Enterprises - Your One Stop IT Experts
 
Thanks for that.

When I had it as a sub, it worked fine - sent the message directly - never
went to outbox. Once I declared it as a function, it just stopped
completely - click the button and nothing at al happens, no error, no
activity!

Ideally I wanted it in a module so I could break down and reuse the code
more easily on other forms I plan to design.

I guess if all else fails I will have to put it back as a sub in the form
code!

Thanks for you help any way.

Jeremiah Ellison said:
I loaded your code and after setting up references to include MS Outlook
Click to expand...
Object Library it worked just fine. However, the
email had not been sent, it was just put in the outbox and I could not
Click to expand...
find a method to send the emails. Was that what you're
missing?

On another note, the function you have isn't really a function, just a sub
Click to expand...
declared as a function. Functions return values,
subs don't. Either can be placed in a form module or a standard module, it
Click to expand...
makes little difference except with the calling
procedures. The only good reason to put it in a standard module would be
Click to expand...
for expanding use to multiple forms or not form
related at all. It looks like it still belongs in the form module.

If all else fails, just go with what you had before. If size is an issue
Click to expand...
in viewing and maintaining your code then just put a
few extra line breaks before and after the sub to distinguish it from the other code.

Hi

I am using Outlook Automation to send emails and assign tasks in a help desk
database I am, designing . I did have them as subs in my DB, but as they
started to grow, I decided to break them out and put them into a module as
functions. However, now I have done this nothing works! I click my command
button and nothing happens - no errors, no outlook.

Below is my code for sending a confirmation email via Outlook (both the sub
for calling the function and the function itself which resides in a module).

Can anyone suggest a reason why it would have stopped working? I considered
the possibility that these did not port over to functions well but I don't
know so I thought I'd ask the experts.

---

Private Sub cmdSendEmail_Click()

On Error GoTo Err_cmdSendEmail_Click

Me!HLP_Email.Value = Forms.SYS_Hardware!HDW_Email.Value

Call fncHelpDeskSendEmailOutlook

Exit_cmdSendEmail_Click:
Exit Sub

Err_cmdSendEmail_Click:
'MsgBox Err.Description
Resume Exit_cmdSendEmail_Click

End Sub

---

Function fncHelpDeskSendEmailOutlook()

Dim strEmail As String
Dim strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'**creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

'***email address to send to
strEmail = Forms![Sys_HelpDesk Subform].[HLP_Email]

'***main body of email
strBody = strBody & "Dear " & Forms![Sys_HelpDesk Subform].[HLP_ReportedBy]
& Chr(13) & Chr(13)

strBody = strBody & "Your IT Support issue has been logged on " &
Forms![Sys_HelpDesk Subform].[HLP_DateReported] & " at " &
Forms![Sys_HelpDesk Subform].[HLP_TimeReported] & _
", and has been assigned Call Number " & Forms![Sys_HelpDesk
Subform].[HLP_CallNumber] & "." & Chr(13) & Chr(13)

strBody = strBody & "Regards" & Chr(13) & Chr(13)
strBody = strBody & "Keith" & Chr(13)

'***creates and sends email

With objEmail
.To = strEmail
.Subject = "IT Support Request - Confirmation"
.Body = strBody
.Send
End With
'****end *****

Set objEmail = Nothing
'****closes Outlook
'objOutlook.Quit

End Function
Click to expand...

Jeremiah Ellison
Ellison Enterprises - Your One Stop IT Experts
Click to expand...
 
Back
Top