SEND EMAIL and ATTACH PDF in ACCESS 2010 1

[clarkgraff]

Greetings and thanks in advance for any and all assistance.

I have created an Access database for use in Sharepoint that will ultimately be presented on our website in the technical support area. My desire is to provide clients with a place to report their problems. The database requires that the client use their email address in the first field, and then choose from a number of fields with pull down menus to focus the clients' report and a DESCRIPTION field that will contain detailed report written by the client.

My wish is to then allow the client to CLICK a button which will

1. SAVE the RECORD

2. CREATE a PDF from the OPEN RECORD

3. ATTACH that PDF to and EMAIL

4. SEND that email to the email address in the CURRENT RECORD

5. CC the EMAIL to a fixed email address ie. techsupport@mycompany.com

6. CLOSE the RECORD and NOT provide a way for the client to BACKUP and SEE any RECORDS including their own

I managed to get the data base working in ACCESS but I still cannot get the code correct to make this work the way I have designed.

Please advise.

Thanks

 

[kjv1611]

One note on this that you may not have thought of yet. If the end-product is going to be on SharePoint, then you probably need to build the front-end portion of things via SharePoint and InfoPath for the form functionality. I say that, b/c you'll have to build the code that does your automation pieces there, I'm pretty certain. If you build it to work 100% in Access, and then try to move it to SharePoint, you'd end up having to redo your printing and such in InfoPath... I think.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[clarkgraff]

Greetings KJV1611
Thanks for the post.
This will be used with SharePoint and ultimately on the web as a client TechSupport database. So I am not opposed at all to using SharePoint for the front end of things.
I will be exporting the access database that I have built so far to SharePoint tomorrow for testing and any guidance you can offer would be great.
Below is the sort of functionality I am looking to acheive, perhaps you could comment?
thanks

1. Client (via website) CLICKS the CREATE SUPPORT TICKET button (thereby running MACRO)
2. MACRO creates NEW RECORD in EDIT mode
3. MACRO ADDs serialized, unique number to ID field
3. MACRO CONCATENATES CURRENT DATE and TIME in the DATE-TIME Field
4. MACRO GOES TO EmailAddress field and waits for Client INPUT.
5. Client enters email address and chooses from pull down menus the criteria of the issue and types in detail in the TEXT field at the end.
6. Client CLICKS on SUBMIT_REPORT button
7. MACRO SAVES_RECORD and changes to READ ONLY MODE
8. MACRO SEARCHES existing RECORDs for comparable issues.
9. MACRO DISPLAYS list of FOUND records
10. FOUND_LIST_FORM provides for Client to CHOOSE any of the FOUND_RECORDs in the list for review.
11. REVIEW_LIST_FORM provides Client with BUTTONs for NEXT_RECORD, PREVIOUS_RECORD, SOLVED and SUBMIT_TO_TECH_SUPPORT.

 

[kjv1611]

Hmm, I forgot how much more integrated Access 2010 is (assuming that's the one you're using)... it may be a little less of an issue than it was before... give it a go, and see what turns out... post back with additional questions once you get the basic there...

I've not had a definite reason to do so yet myself, but I can think of one right now that I did at least consider some time back when I first read about the closer integration.. I'll have to test with that and see.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[clarkgraff]

Yes I am using Access 2010 and I have the basic database functionality up and running.
IE
1 CREATE NEW RECORD
2. CONCATENATE TIME and DATE STAMP INTO DateCreated Field
3. Client enter email address
4. ALL pull down menus for the narrowing of client issue
5. TEXT field for detailed client entered issue.

What I need is some help getting the code to run behind a button that
1. Takes client entered emailaddress and creates email
2. COPY data from DateCreated and ID CONCATENEATEs those 2 fields into SUBJECT LINE of email
3. COPY all the results of the remaining fields into the BODY of the text
4. DUPLICATEs this email and sends to hard wired tech@mycompany.com

Thanks in advance

~clark

 

[kjv1611]

Well, if what I've been reading about the better integration is true, then it sounds like you can do all what you are wanting to do without worrying about the SharePoint side of things. So if that's the case, you may be in pretty good shape.

I unfortunately don't have time right now to get into the nitty gritty of it, but the answers can be found pretty easily. Here are some quick thoughts, based on your numbering:

1. email - Consider whether you want to do so with or without Outlook involvement, and whether you want to preview the message before sending, or just have it send... then go searching.. Also, if you use Outlook, there have been (not sure if still an issue) times when you'd be prompted with a MsgBox confirming you wanting to send through Outlook if you sent to more than one email address...

2. Well, you'll do that as part of #1 really.. You'll just have the Email fields reference the form fields.... Such as: MyEmailObject.Subject = MyForm.Field1 & " " & MyForm.Field2

3. For this one, you'll definitely want to use an Access Form object and a Control object in your code, and loop through the controls on the form, specifying the fields to include or exclude - the details of how would be best determined by how many fields we're talking about - how they are similar/different, and how many are excluded as well.. You CAN filter based on ControlType as well as the name and content..

4. For the last part, wouldn't you just put the "copied" address to either the CC or else BCC field in the email object? That would automatically copy you.. CC if you want it seen, BCC if you don't want it seen.

Your thoughts?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[clarkgraff]

Thanks for the comments. I have been going over this plan and I think there is a better way to achieve my goals.
On the website the user will
1. choose CREATE SUPPORT TICKET
2. application will open a dialog with one field for email address and a button for SUBMIT
3. User enters their email address and chooses SUBMIT
4. Application sends email address to access database on our internal sharepoint and creates a new record
5. Access database sends the form to that email using the Access feature externalData\collectData\createEmail
6. user enters data into form and returns email to database
7. tech support can manage issues from Access or Sharepoint internally

I think this works better because it keeps the database OFF of the web and safe.

I have the database created with all the pull downs and have successfully ported it to sharepoint. I have created a specific email address on our exchange server to be used for this purpose.

The problem I am having at the moment is when I try to use the Access feature described above it is greyed out and I cannot seem to find out why?

Any ideas>>

thanks again

 

[clarkgraff]

I think I have discovered why the collectData\createEmail option was greyed out.

I created and completely new database with all of the same fields and did not publish it to Sharepoint.

That feature was now AVAILABLE.

So I tested that feature and had some success.

Now I have published this version of the database to Sharepoint and now that option is greyed out again.

Are you not allowed to use that externalData\collectData\createEmail functionality from an Access database published on Sharepoint?

thanks