Send Emails from outlook

[WhiskyLima]

Hey guys, so here's my latest issue. I have a database where I would like to send emails from outlook. So I had a read around and found out how to create your own buttons with vba in outlook. Which is great so I set to work on this. The code below is initiated when a user form opens .

    Dim dbs As DAO.Database
    Set dbs = DAO.OpenDatabase("c:\mrl\frontend.accdb")
    Dim i As Integer
    
    Dim rst As DAO.Recordset
    Set rst = dbs.OpenRecordset("SELECT [Contact ID], [First Name], [Last Name] FROM Contacts")
    rst.MoveFirst
    i = 0
    With Me.cmb
         .Clear
         Do
            If Not IsNull(rst.Fields("[Contact ID]")) Then
             .AddItem
             .List(i, 2) = rst.Fields("[Contact ID]")
             .List(i, 1) = Nz(rst.Fields("[First Name]"), "")
             .List(i, 0) = Nz(rst.Fields("[Last Name]"), "")
             i = i + 1
             rst.MoveNext
             End If
         Loop Until rst.EOF
     End With
    Set dbs = Nothing
    Set rst = Nothing

Ok great that work, now I have a combobox with the names and id numbers of all of the clients from the database. That's all good. The next thing was to then use this code (below) on a button to tell it to store the details of the selected email as variables. It then passes the content of the email to another function which removes the special characters and passes the result back to the variable (this stops any issues with SQL later). After the user has selected a name from the list, they press the button and it creates a new entry in the database with the details of the email...

Dim olItem As Outlook.MailItem
Dim sText As String
Dim sSubject As String
Dim sFrom As String
Dim sTo As String
Dim sDate As Date
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = DAO.OpenDatabase("c:\mrl\frontend.accdb")
 
Set olItem = ActiveExplorer.Selection.Item(1)
sText = olItem.Body
sSubject = olItem.Subject
sFrom = olItem.SenderEmailAddress
sTo = olItem.To
sDate = olItem.SentOn
'This section of code removes all of the special characters from the email content to stop it messing with the SQL code.
ValidateString sText
strSQL = "INSERT INTO SavedEmails ([Sender Name], [Received], [Subject], [Content], [ClientID]) VALUES ('" & sFrom & "', '" & sDate & "', '" & sSubject & "', '" & sText & "', '" & cmb.Column(2) & "')"
dbs.Execute strSQL
Set dbs = Nothing
Set rst = Nothing

Ok, still with me? Well that too works fine, however there is an issue, I cant do this while the database is open as it says the database has been placed in a state blah blah, its obviously open and is read only.

I suppose my question is this, is there are way to do this with the database open? If not, is there any way to tell it to close access when its done because at the moment it leaves access running in the background and wont allow me to open the database until I force the background process (msaccess.exe) to close.

As always any and all help really is greatly appreciated.

P.s just so you understand my thinking here, the other route I could have taken is to import the emails from the inbox and then assign them from the database but this is clumsy and means when I have read an email I think is important to a certain client in the database I would then have to go to the database, find the email and then assign it and I don't want that.

Many, many thanks in advance guys, I really do appreciate it!

[ranman256]

1 .You dont need that 1st box of code to fill a form. Connect the form to a query and all fields fill themselves. Zero code needed.

2. I email from the database everyday and it's always open and gives no errors. If you are getting the ACCESS STATE... you must be running too many applications, or trying to edit while running.

Open outlook (in access)
run a query that gets the emails
for each record
send email
next
close outlook.

I see an insert statement in the 2nd block of code...dont.
try just emailing. Then you can update afterwards.

[June7]

Is this code in Outlook?

[ItsMe]

Is this code in Outlook?

I think it is.

Maybe you can use the close and quit methods just before:
Set dbs = Nothing
Set rst = Nothing

Another consideration, in addition to closing things down and tidying up, would be to dedicate a DB that links to the original, c:\mrl\frontend.accdb, DB. The dedicated DB would be only for Outlook.

[June7]

I use this command syntax to insert records to a table. Doesn't matter if the destination db is open or closed:

CurrentDb.Execute "INSERT INTO Table1 IN '\\servername\path\database.accdb' SELECT * FROM tablename;"

Another example (this one is going the other way, pulling data from another db but should be adaptable to push data):
strSourceFields = "ID, [Name], Condition, [_Latest], PCISource, Use, Include, Active, [Date], Area, FAAID"
strDestFields = "SectionID, BranchName, Condition, Latest, Source, Use, Include, Active, InspectionDate, Area, FAAID"
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM PaverData_InspectionsAllYears"
DoCmd.RunSQL "DELETE FROM PaverData_MajorMRAllYears"
DoCmd.RunSQL "INSERT INTO PaverData_InspectionsAllYears(" & strDestFields & ") SELECT " & strSourceFields & " FROM [" & strSourcePath & "].zUser_InspectionsAllYears;"
DoCmd.RunSQL "INSERT INTO PaverData_MajorMRAllYears(SectionID, BranchName, Use, ConstDate, Include, Active, FAAID) SELECT ID, [Name], Use, [Date], Include, Active, FAAID FROM [" & strSourcePath & "].zUser_MajorMRAllYears;"
DoCmd.SetWarnings True


There is no db object instantiated so nothing to close or quit or kill and no object variables to wipe out.

But I have never coded behind Outlook.

[WhiskyLima]

Hi June7 Thanks for your input, im going to try the code you suggested to insert the statement without opening the database, that would be perfect. I am indeed coding this from outlook as I don't want the mess that comes with importing all emails into access and then trying to sort them from there. I have a very picky client who would not want the hassle of having to look for the email in the database after already having read it in outlook. Anyway, I'll let you know who it goes!

[WhiskyLima]

Ok, it doesn't seem to like the CurrentDb.Execute command. Im guessing that's because there is no current database open? Is there another way to push the information into an unopened database? Bearing in mind that this is being executed from outlook? Cheers for all your help, I think this may be the correct path to take

[WhiskyLima]

Ah ha! Ok I have just realised that of course, currentDb will work if the database is open.. as soon as I wrote it I thought about it and it clicked so yes indeed this does work as long as the database is open, which is perfect! Thank you for you help I really appreciate it! Turns out programming outlook is exactly the same as access!

set dbs = CurrentDb
strSQL = "INSERT INTO SavedEmails ([Sender Name], [Received], [Subject], [Content], [ClientID]) VALUES ('" & sFrom & "', '" & sDate & "', '" & sSubject & "', '" & sText & "', '" & cmb.Column(2) & "')"
dbs.Execute strSQL

[June7]

My example CurrentDb.Execute code is running in one db to pass data to a closed db. Since your code is behind Outlook, are you saying the CurrentDb worked? The code recognizes Outlook as a database? Interesting.