Report/Email 1

[Advisedwolf]

I have a database of purchase orders.
What I need to do is run a report that lists all purchase orders per supplier. Then, I need it to email the relevent purchase orders to each supplier.
I.e: All purchase orders for supplier A, get emailed to supplier A,

I have a seperate table, that lists all the supplier names, and the respective email addresses.
So I am assuming, the report needs to reference this, if the supplier on the purchase order table matches the supplier name on the supplier database, then email to the associated email address.

I hope I have made this clear, any advice/guidance on how to achieve this will be appreciated.

Thanks

 

[GingerR]

What have you got so far? Do you have the report created? How do you wish to do this? Clicking a button? Do you have a form created that has a button that launches this functionality? Have you tried SendObject?

 

[Bobnz]

You could create an outlook object.

Sub SendMessage(Optional AttachmentPath)
   Dim objOutlook As Outlook.Application
   Dim objOutlookMsg As Outlook.MailItem
   Dim objOutlookRecip As Outlook.recipient
   Dim objOutlookAttach As Outlook.Attachment
   Dim supplier As Variant
   supplier = recipient
   
    
   ' 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.
     Set objOutlookRecip = .Recipients.Add(supplier)
      objOutlookRecip.type = olTo
     

      ' Add the CC recipient(s) to the message.
      Set objOutlookRecip = .Recipients.Add("xxxx@xxx.com")
      objOutlookRecip.type = olCC

      ' Set the Subject, Body, and Importance of the message.
      .Subject = "Order"
      .Body = "Please fill the attached order: " & vbCrLf & vbCrLf
      .Importance = olImportanceHigh  'High importance

      ' Add attachments to the message.
      If Not IsMissing(AttachmentPath) Then
         Set objOutlookAttach = .Attachments.Add(AttachmentPath)
      End If

      ' Resolve each Recipient's name.
      For Each objOutlookRecip In .Recipients
         objOutlookRecip.Resolve
         If Not objOutlookRecip.Resolve Then
         objOutlookMsg.Display
      End If
      Next
      .send
    MsgBox " Your Order has been sent"
   End With
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
End Sub

 

[Advisedwolf]

In response to the first thread,
At this stage, I only have a select query created that lists all the open purchase orders. I have then created a report from this query, that has a grouping level per each vendor, this forcing a new report page on each vendor change.
I wasn't sure where to go next, but It would be nice to have a final solution where a macro performs the relevant actions from an option on switchboard.

As to the second reply, I will explore the scenario you presented me, thankyou for both your responses

Thanks

 

[GingerR]

If all vendors are in one report, everyone you send it to will see all info for all vendors.

The typical solution is to have a form with a combo box on it, whose RowSource is your Vendor table. Your report references this combo box, so the report only opens for the Vendor you pick. You pick a vendor. Then you click a button which sends the Report. If you set the combo box up to include the vendor's email address in one of the columns, then this is an easy way to reference where to send it. Say your combo box is three columns, VendorID, VendorName, VendorEmail. Then in your button's ONCLick event, you use the Send Object (or Bob's code) and get the email address from me.comboBox.column(2).

There are a ton of posts here about emailing reports. Here's one:
thread703-937575

 

[Advisedwolf]

Thanks for your help here Ginger.

You've given me a great starting point on how to achieve this. I will explore down this avenue as it sounds like an ideal solution on how i'd like this to work!

Thanks!