How do i run a Public sub from a macro

[Brendanpeek]

Hello

i have tried everything but i cant seem to get my module called CalEmail to
run from a macro (im new to VBA). the module starts with

Public Sub calemail(Optional AttachmentPath)

is this where im going wrong, please help

Thank you for your time.

 

[Steve Schapel]

Brendanpeek,

You haven't told us what you are doing, but my guess is you are using a
RunCode action in your macro. Is that right? If so, I think you will
need to change the VBA procedure from 'Public Sub' to 'Public Function'.

 

[Brendanpeek]

Yes im using runcode, thank you i will try that now.

 

[Brendanpeek]

I just tried it with the changes you suggested and access displayed this msg
when i tried to run the macro, "the expression you entered has a function
name that STREAM cant find.

i will give you my code below just in case the mistake is in there.

Option Explicit

Public Function calemail(Optional AttachmentPath)
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim FilePathStrg As String
Dim FileNameStrg As String

If DCount([Allocated To], [queries]![Cal Due Email]) > 0 Then

FilePathStrg = "C:\Documents and Settings\Brendan.Mills\Desktop\Equipment
DB\"
FileNameStrg = "Items due for calibration" & ".rtf"

If DoesFileExist(FilePathStrg & FileNameStrg) = True Then
Kill FilePathStrg & FileNameStrg
Else
End If

DoCmd.OutputTo acOutputReport, "Cal Due Email", acFormatRTF, FilePathStrg
& FileNameStrg, False

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
' Add the To recipient(s) to the message. Substitute
' your names here.
Set objOutlookRecip = .Recipients.Add("Matthew Horlock")
objOutlookRecip.Type = olTo
' Add the CC recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("(e-mail address removed)")
objOutlookRecip.Type = olCC
' Set the Subject, Body, and Importance of the message.
.Subject = "Items Due For Calibration"
.Body = "This Email should contain an attachment with details of items
required for calibration, please forward on to whom it may concern." & vbCrLf
& vbCrLf
.Importance = olImportanceHigh 'High importance
' Add attachments to the message.
If IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add("C:\Documents and
Settings\Brendan.Mills\Desktop\Equipment DB\Items due for calibration.rtf")
End If
' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Set objOutlookRecip = Nothing
Set objOutlookAttach = Nothing

Else: Exit Function
End If
End Function

 

[Steve Schapel]

Brendanpeek,

What exactly do you have in the Function Name argument of the RunApp macro?

DCount([Allocated To], [queries]![Cal Due Email]) > 0
isn't correct. For one thing, you need to wrap the arguments in ""s,
and for another the expression [queries]![Cal Due Email] does not
compute... there is no such thing as [queries]. It also doesn't really
make sense to use the name of a field in this context either. Maybe you
mean this:
DCount("*", "Cal Due Email") > 0

Having said that, I am not sure but I would not expect this to result in
the error message you got.

--
Steve Schapel, Microsoft Access MVP

I just tried it with the changes you suggested and access displayed this msg
when i tried to run the macro, "the expression you entered has a function
name that STREAM cant find.

i will give you my code below just in case the mistake is in there.

Option Explicit

Public Function calemail(Optional AttachmentPath)
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim FilePathStrg As String
Dim FileNameStrg As String

If DCount([Allocated To], [queries]![Cal Due Email]) > 0 Then

FilePathStrg = "C:\Documents and Settings\Brendan.Mills\Desktop\Equipment
DB\"
FileNameStrg = "Items due for calibration" & ".rtf"

If DoesFileExist(FilePathStrg & FileNameStrg) = True Then
Kill FilePathStrg & FileNameStrg
Else
End If

DoCmd.OutputTo acOutputReport, "Cal Due Email", acFormatRTF, FilePathStrg
& FileNameStrg, False

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
' Add the To recipient(s) to the message. Substitute
' your names here.
Set objOutlookRecip = .Recipients.Add("Matthew Horlock")
objOutlookRecip.Type = olTo
' Add the CC recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("(e-mail address removed)")
objOutlookRecip.Type = olCC
' Set the Subject, Body, and Importance of the message.
.Subject = "Items Due For Calibration"
.Body = "This Email should contain an attachment with details of items
required for calibration, please forward on to whom it may concern." & vbCrLf
& vbCrLf
.Importance = olImportanceHigh 'High importance
' Add attachments to the message.
If IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add("C:\Documents and
Settings\Brendan.Mills\Desktop\Equipment DB\Items due for calibration.rtf")
End If
' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Set objOutlookRecip = Nothing
Set objOutlookAttach = Nothing

Else: Exit Function
End If
End Function