Open and Email Report from Combo Box

[RAM022]

Hi,

I have over 10 reports from queries that I want to open from a Combo Box. The combo box is working perfect with the name of the reports, I just need to create a button that opens and emails the selected report on the combo box.

I appreciate any help. My experience with access is very limited. Thanks.

[zmbd]

RAM022 > outlook or other source?

What is the actual value of the combobox?
If it returns the actual name of the report as its value, NOT THE DISPLAYED NAME, then you can simply use the control's value in the docmd.sendobject (read more) method which is one of the easier methods for sending simple emails. Otherwise, you may have to return the text value or the column value of the control

Remember to include error trapping in case the user cancels the email instead of sending.

[hvsummer]

@RAM022:
welcome to bytes.com
you can select your report from a query (rowsource of combobox) which sql like this:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
(MSysObjects.Type)= -32764 ORDER BY MSysObjects.Name

if you don't need to automaton a list of email address and your signature prepaired on email, just do as zmbd's suggestion.

but if you need to automaton outlook that carry lot of cases. then you have to give us more detail, then we'll talking about outlook automaton :)

[zmbd]

hvsummer
That is an "undocumented" feature of on of the access system tables, subject to change without notice by MS.

Let's wait for OP to answer the question about how the combobox is actually designed so that we can provide more bullet proof directions.

[RAM022]

Thanks,

My combo box is is getting the values with a query like HVSUMMER said.

I managed to also get the button to open the report with an Onclick event:

Private Sub OpenReport_Click()
    DoCmd.OpenReport Forms!ByDirectorates.ComboReports, acViewReport
End Sub

But when I try to create and additional button and write on the code bulder, it messes up the one tha I already have on the form.

Is there a way to change from code builder to macro builder after I started creating an event?

Appreciate all your help.

[hvsummer]

can you tell us more detail about

Is there a way to change from code builder to macro builder after I started creating an event?

what do you mean code builder ? what do you mean macro builder ?
for me, those code/macro are the same thing == so I know something wrong here..

But when I try to create and additional button and write on the code bulder, it messes up the one tha I already have on the form.

What are you planing to do ? please tell me the purpose, not your previous action...

I could suggest and give you some example if you've given the clean picture.

[zmbd]

> What version of Access/Office are you using?

RAM022 Is there a way to change from code builder to macro builder after I started creating an event?

You really shouldn't go back to the macro builder to do this, if what you are doing now in VBA is making a mess of things, I will guarantee that attempting to do this using the macro builder will compound the issues.

RAM022 But when I try to create and additional button and write on the code bulder, it messes up the one tha I already have on the form.

The second button will have its own set of events and associated VBA.

From the code you have now, it appears that you have a command button control named [OpenReport]

I'm guessing that you attempted to add a button named [btnEmailReport] or [btnSendReport]. What is not clear is how you attempted to add the code for this second command button. You MUST do this thru that button [btnEmailReport]'s property-sheet-on_click event. If you attempt this by direct entry into the VBA-Editor then the code you create will not be properly associated with the control (There is a way to do this; however, for beginners or for those rusty with the process I recommend initially avoiding such methods! :) )

>> Here's the catch, when adding a control to a form, depending on which version of Access you are using, how you invoke the builder, and if you have the Wizard enabled, the Wizard can really mess things up by creating an embedded macro and when you go to write the VBA, there's no association between the control and the VBA code. To correct this, you have to back in to the form, find the control, and select "Event Procedure", click on the button with the three dot (ellipses) and then move your VBA script within the form's code module.<<

HVSummer does have one thing right here, we need a clearer picture of what it was that you attempted to do with the second button and by what method you attempted to add the second button (directly from the toolbox, cut-n-paste of the first button, etc...).

=================================

]HVSummerwhat do you mean code builder ? what do you mean macro builder ?
for me, those code/macro are the same thing

PLEASE do NOT confuse people

In MS-Access: unlike in Excel (where your background seems to be):
1) CODE BUILDER is used to create VBA in ACCESS
> A control that has VBA behind the event will show
[Event Procedure]
in the property sheet for that event

2) MACRO BUILDER is used to create ACCESS MACRO SCRIPT
> A control that has an embedded macro behind the event will show
[Embedded Macro]
in the property sheet for that event

In Access development, these two terms: Macro and VBA/Code, are NOT interchangeable.

Of the two, unless creating a sharepoint site, autoexec, and/or autokeys macros - Access VBA is usually more flexible and able to do more than Access Macros - even MS own developers have commented that the Access-Macro was not intended to replace VBA in Access
((MSDN Blogs > Clint Covington: Software design, Microsoft Office Access > Reader question about macros and VBA ))...

]HVSummer== so I know something wrong here..

Sorry, you know no such thing based on this information.

>> When providing direction, we must carefully consider our background and area of expertise before making statements of fact that are not in evidence - unless we commit he grave error of misleading our fellow members.

This has been mentioned to you before by another moderator and myself several times.<<

[hvsummer]

@zmbd and guy:
oh sorry, recently I code a lot on Excel vba so I forgot macro/vbaCode aren't the same thing in Access :D

Macro Script in Access is an built in function that lets user do some simple task, it sometime helpful but usually not really flexible (compare to vba code).

when I mean something wrong, I mean he doing some thing wrong, not the information he provided wrong.

Ok, that's my fault to comment on this.

[RAM022]

Thanks,

I am using Access 2013. I got everything working right now on a pop up form from my main form. It looks like this:

Private Sub EmailReport_Click()
On Error GoTo EmailReport_Click_Err
 
DoCmd.SendObject acReport, Forms!ByDirectorates.ComboReports, "PDFFormat(*.pdf)", "", "", "", "", "", True, ""
 
 
EmailReport_Click_Exit:
    Exit Sub
 
EmailReport_Click_Err:
 
    Resume EmailReport_Click_Exit
End Sub
 
Private Sub OpenReport_Click()
DoCmd.OpenReport Forms!ByDirectorates.ComboReports, acViewReport
End Sub
 

Everything is working fine, with error trapping and on pdf format like I wanted it. I just might have to change the way my combo box is getting its values from in case Microsoft changes something in the MSysObjects table like you mentioned.

I appreciate all your help.

Now I have a different question but I guess I will start a differente thread not to get this one too crazy. Thanks again.