New to db programming! Any help appreciated!

[hydro]

Hi,
I am new to database programming but am trying to create a booking system in my spare time for a friend that has just started a small business. Here is what I am trying to achieve using Access 2007.
1) A user submits a quote request form online which is then sent via email.
2) A quote should then sent back via email after a price has been decided with a form attached requesting more information (e.g address)
3) The user fills in the form and the data is entered back into the db.
So far I have managed to scan the inbox and pull out the relevant information for the new emails requesting quotes and import this into the database (email2DB not sure if this is the best way to do this). My next step is to try and create some kind of automated mail merge to be emailed to the customer after the offer price has been inserted into the database.
I’m not really sure whether reports or macros are the way to go or whether I should look more into a VB program that links somehow with the database.
Any help would be greatly appreciated
Thanks

 

[Trevor G]

Hi,
I am new to database programming but am trying to create a booking system in my spare time for a friend that has just started a small business. Here is what I am trying to achieve using Access 2007.
1) A user submits a quote request form online which is then sent via email.
2) A quote should then sent back via email after a price has been decided with a form attached requesting more information (e.g address)
3) The user fills in the form and the data is entered back into the db.
So far I have managed to scan the inbox and pull out the relevant information for the new emails requesting quotes and import this into the database (email2DB not sure if this is the best way to do this). My next step is to try and create some kind of automated mail merge to be emailed to the customer after the offer price has been inserted into the database.
I’m not really sure whether reports or macros are the way to go or whether I should look more into a VB program that links somehow with the database.
Any help would be greatly appreciated
Thanks

As an option I think you would be better of on a different platform if trying to sort this via websites.

Have you heard of Joomla? It is an open source piece of software which a number of programmers have created using php, html and java to name a few features.

What you would need is to have your web space provider to allow you to use a MySQL database which holds the records, you don't need any additional software as you can run this all on the web so updating monitoring emailing clients who register is a must.

The only other thing that you would have to do is create a unique template, there are many free stuff and also some good applications that will allow you to select your own options and it will build all the code for you. I use something called Artisteer.

So my recommendations are:

Look at what your web provider will supply you (at least 1 MySQL database)
Look then to download and install Joomla 1.5
Create a suitable template with Artisteer

Good luck

 

[hydro]

Thanks for your suggestion trevor. The website is only used for the user to fill in information to request a quote.

My friend is currently doing all this by hand and takes him a while!

My cousin is a Web Designer and has talked about Joomla before I will have to get more information off him.

Basically i just want a system that he can keep track of everything. Send out the email when an asking price has been entered into a field would be good but not the main thing i am trying to achieve. he also needs to print out all the addresses for the people that have accepted the offers onto avery labels and eventually print the cheques from the database so he doesnt have to hand write them all.

If i could automate the whole process that would be great but even if i could make it slightly easier he would appreciate it.

Thanks again for your help

 

[Trevor G]

Ok you are going to need to make some queries and some other types of either reports which can handle the labels and possibly use excel as well.

I have attached something to my downloads side of my website which will asssit you. There is an excel spreadsheet and database, the database was created for an events company and there are email forms in there as well as possible mail merge for word.

http://www.wuit.co.uk/downloads.htm

The first item in the list is what you want.

 

[hydro]

Thanks alot for that im making a little bit of progress now. I have managed to send a simple mail from access but when i try and call information from my access database i get an error at the 'me.'

olmailmessage.Subject = Me.txtsubject.Value
olmailmessage.Body = Me.txtbody.Value & vbCrLf
olmailmessage.Attachments = varAttach

what library do i have to reference to use the me. commands? I tried to load the DAO 3.6 object library ( which is what i think uses the me. commands to import data from your db) it fails to load??

thanks for your help that link was very useful

 

[Trevor G]

You have to set the reference to Outlook. The listed References are shown here.

 

[hydro]

Thanks.

I had the outlook reference set already but i didnt have some of the others.

If i use
olmailmessage.Subject = "subject"
olmailmessage.Body = "body of email"

it will send the email but if i cannot get it to pull data from the database.

I have a table named table1 and and the fields 'username' and 'querydate' is it possible to pull the information and use it in the body of the email?

so for example

Hi 'username'

you submitted a query on 'querydate'

Thanks

 

[Trevor G]

This code is being used via a form but is using a query so it sends out multiple emails, I am sure you can adapt it, it is also sending an attachment (form to fill out, isn't this one of the things you want to do?)

Sub sendemails()
Dim olapp As Outlook.Application
Dim olmailmessage As Outlook.MailItem
Dim olrecipient As Outlook.Recipient
Dim StrMsg As String
Dim varAttach As Variant
Dim rcdSQL As Variant

Dim db As DAO.Database
Dim rcd As Recordset

Set db = CurrentDb()

Set rcdSQL = db.OpenRecordset("SELECT * FROM qryRegionalUKCeremony WHERE [AwardEventAttendance]='UK Ceremony'")

Set olapp = New Outlook.Application
varAttach = "C:\National Training Awards\Invitation Mailing\Application Form.doc"



Do Until rcdSQL.EOF

If rcdSQL!.Value <> "" Then
Set olmailmessage = olapp.CreateItem(olMailItem)

olmailmessage.Recipients.Add (rcdSQL![email].Value)

olmailmessage.Subject = [COLOR=red]Me.txtsubject.Value[/COLOR]
olmailmessage.Body = [COLOR=red]Me.txtbody.Value & vbCrLf[/COLOR]
olmailmessage.Attachments = varAttach



'If blnKnownRecipient = True Then
olmailmessage.Send
Set olmailmessage = Nothing
End If
rcdSQL.MoveNext


Loop
'olapp.Quit
Set olapp = Nothing
End Sub

By the way if you send from access to Outlook you may get a message that states a program is trying to access email address etc. Here is a link to allow the automatic yes to be clicked otherwise you end up clicking yes a lot of times.

[URL]http://www.contextmagic.com/express-clickyes/[/URL]