Downsizing

[Thompyt]

I have a main form [Start] and I can click a button to start a neqw email with a PDF attachment. The VBA is "Private Sub Send_Min_Start_Click()." I also have an email button on the report [Weekly SITREP III] to do the same "Private Sub Send_Sum_Click()."



I would like to be able to have it look at one code set instead of having 2, both are the same. What do I do to have the report look at the form, or the form look at the report in VBA and/or access to run that command set? This would halve my VBA code.

Thanks

Wayne

[pbaldy]

I would create a public function and call it from both places:

http://www.baldyweb.com/Function.htm

Alternatively, you could make one of your existing subs Public, and call it from the other. I prefer stand-alone subs/functions though, when I want to call something from multiple locations.

[Thompyt]

Thanks I'm pretty simple minded and not too creative.

Public Function Daily_Mail_Click()

Private Sub Send_Daily_Click()
If (Action_Complete) = False And Date_Closed <> Now Then
 Date_Closed = Now
Else
On Error GoTo ErrorMsgs
 Dim objOutlook As Outlook.Application
 Dim objOutlookMsg As Outlook.MailItem
 Dim objOutlookAttach As Outlook.Attachment
 Dim strBody, strAddresses, strSubject As String
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
    .Subject = "Title- " & Format(Date, "dd mmm yyyy")
    .Body = "For your use." & vbCrLf & vbCrLf & "V/R" & vbCrLf & vbCrLf & "Name" & vbCrLf & vbCrLf & _
            "Org 1" & vbCrLf & "Org 2" & vbCrLf & _
            "Addy 1" & vbCrLf & "Addy 2" & vbCrLf & "Phone" & vbCrLf & _
            "Email Addy"
    DoCmd.OutputTo 3, "Daily Actions", acFormatPDF, "C:\Temp\Daily Actions - " & Format(Date, "dd mmm yyyy") & ".pdf", , 0
   .Attachments.Add ("C:\Temp\Daily Actions - " & Format(Date, "dd mmm yyyy") & ".pdf")
   '.To = "Adressee"
   .Display
    DoCmd.Close acReport, "Daily Actions"
    Kill "C:\Temp\Daily Actions - " & Format(Date, "dd mmm yyyy") & ".pdf"
  End With
  Set objOutlookMsg = Nothing
 Set objOutlook = Nothing
 Set objOutlookAttach = Nothing
  Exit Sub
ErrorMsgs:
 If Err.Number = "287" Then
 MsgBox "You clicked No to the Outlook security warning. " & _
 "Rerun the procedure and click Yes to access e-mail " & _
 "addresses to send your message."
 Else
 MsgBox Err.Number & " " & Err.Description
End If
End If
End Sub

End Sub

Then in all other places put :

Public Function Daily_Mail_Click()

For that Private Sub?

Thanks

[pbaldy]

Uh, no. Let's take the easy way out. Change:

Private Sub Send_Daily_Click()

to

Public Sub Send_Daily_Click()

then in the other event, take out all the code and just put:

Call Send_Daily_Click()

[Thompyt]

Public Sub Send_Daily_Click()
If (Action_Complete) = False And Date_Closed <> Now Then
 Date_Closed = Now
Else
On Error GoTo ErrorMsgs
 Dim objOutlook As Outlook.Application
 Dim objOutlookMsg As Outlook.MailItem
 Dim objOutlookAttach As Outlook.Attachment
 Dim strBody, strAddresses, strSubject As String
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
    .Subject = "Title- " & Format(Date, "dd mmm yyyy")
    .Body = "For your use." & vbCrLf & vbCrLf & "V/R" & vbCrLf & vbCrLf & "Name" & vbCrLf & vbCrLf & _
            "Org 1" & vbCrLf & "Org 2" & vbCrLf & _
            "Addy 1" & vbCrLf & "Addy 2" & vbCrLf & "Phone" & vbCrLf & _
            "Email Addy"
    DoCmd.OutputTo 3, "Daily Actions", acFormatPDF, "C:\Temp\Daily Actions - " & Format(Date, "dd mmm yyyy") & ".pdf", , 0
   .Attachments.Add ("C:\Temp\Daily Actions - " & Format(Date, "dd mmm yyyy") & ".pdf")
   '.To = "Adressee"
   .Display
    DoCmd.Close acReport, "Daily Actions"
    Kill "C:\Temp\Daily Actions - " & Format(Date, "dd mmm yyyy") & ".pdf"
  End With
  Set objOutlookMsg = Nothing
 Set objOutlook = Nothing
 Set objOutlookAttach = Nothing
  Exit Sub
ErrorMsgs:
 If Err.Number = "287" Then
 MsgBox "You clicked No to the Outlook security warning. " & _
 "Rerun the procedure and click Yes to access e-mail " & _
 "addresses to send your message."
 Else
 MsgBox Err.Number & " " & Err.Description
End If
End If
End Sub

Other event put

Private Sub Send_Daily_Click()
  Call Send_Daily_Click
End Sub

When I select the button on the other event nothing happens. If I take out the [Private Sub Send_Daily_Click()] I get an "Invalid Outside procedure"

[pbaldy]

That's calling itself. I just realized you're dealing with a form and a report. That pushes me back towards creating a public function in a standard module, and calling it from both places. You'd have to change things like

If (Action_Complete) = False And Date_Closed <> Now Then

to

If (Forms!FormName.Action_Complete) = False And Forms!FormName.Date_Closed <> Now Then

[Thompyt]

If I take the above code and make a module called [Daily_Send] with this:

[CODE][Public Sub Send_Daily_Click()
If (Report!Daily_Actions.Action_Complete) = False And Report!Daily_Actions.Date_Closed <> Now Then
Date_Closed = Now
Else
On Error GoTo ErrorMsgs
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookAttach As Outlook.Attachment
Dim strBody, strAddresses, strSubject As String
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
.Subject = "Title- " & Format(Date, "dd mmm yyyy")
.Body = "For your use." & vbCrLf & vbCrLf & "V/R" & vbCrLf & vbCrLf & "Name" & vbCrLf & vbCrLf & _
"Org 1" & vbCrLf & "Org 2" & vbCrLf & _
"Addy 1" & vbCrLf & "Addy 2" & vbCrLf & "Phone" & vbCrLf & _
"Email Addy"
DoCmd.OutputTo 3, "Daily Actions", acFormatPDF, "C:\Temp\Daily Actions - " & Format(Date, "dd mmm yyyy") & ".pdf", , 0
.Attachments.Add ("C:\Temp\Daily Actions - " & Format(Date, "dd mmm yyyy") & ".pdf")
'.To = "Adressee"
.Display
DoCmd.Close acReport, "Daily Actions"
Kill "C:\Temp\Daily Actions - " & Format(Date, "dd mmm yyyy") & ".pdf"
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Set objOutlookAttach = Nothing
Exit Sub
ErrorMsgs:
If Err.Number = "287" Then
MsgBox "You clicked No to the Outlook security warning. " & _
"Rerun the procedure and click Yes to access e-mail " & _
"addresses to send your message."
Else
MsgBox Err.Number & " " & Err.Description
End If
End If
End Sub/CODE]

Then in the Button Event Code (On Click) put =Daily_Send()?