Results 1 to 11 of 11
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Unanswered: Sending Email Access VBA

    Converting macros to vba and need to avoid the outlook security -patch
    Is there a free object library out there that will allow me to do this? I thought the outlook redemption objects looked great but then I was told it is $199.
    Dale Houston, TX

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Google CDO; I've used it several times (I'm in an Exchange environment, but I don't think that's required).
    Paul

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    here a script I use to send emails

    Code:
    Function SendMail(Sentto,Sentcc,Subject,Body,SentFrom)
    Dim StrTo
    Dim StrFrom
    Dim StrSubject
    Dim StrText
    Dim iMsg
    Dim iConf
    Dim Flds
    Dim strSmartHost
    copyright = copyright & vbnewline & vbnewline & vbnewline &  vbnewline
    copyright = copyright & "" & vbnewline
    
    '*************************************************************************************
    'This Script Will send out Email but there are 4 line need to be completed
    StrTo       = Sentto   '<= who getting the emails  firstname.surname@tnl.co.nz can use the ; between the email name
    StrFrom     = SentFrom '<= Your email address
    StrSubject  = Subject  '<= What you want the subject to say
    
    StrText     = Body & copyright '<= The message in the message no HTML other wise it get put as SPAM
    set iMsg = CreateObject("CDO.Message")
    set iConf = CreateObject("CDO.Configuration")
    Set Flds = iConf.Fields
    
    With Flds
    	.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    	.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "YOUR.SMTP.SERVER"
    	.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    	.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
    	.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
    	.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    	.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "USER.name.to.send.email"
    	.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "the.password"
    .Update
    End With
    if trim(StrFrom)<>"" then
    	With iMsg
    		Set .Configuration = iConf
    		.To = StrTo
    		.Cc = Sentcc
    		'.bcc = ""
    		.From = Strto
    		.Subject = StrSubject
    		.TextBody = StrText 
    		.Send
    	End With
    End if
    Set iMsg = Nothing
    Set iConf = Nothing
    Set Flds = Nothing
    
    End function
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    Paul - long time no talk Myle thanks for the snippett. What I found out is that to distribute the Outlook Redemption library as a developer I needed topay $199.00 , but as a single user it was free. So what I am doing is at startup see if the dll exists on the machine and is registered, if not I run a command line snippet (silently) to register (regsrvr32) the dll. It is a very simple library to expose and use . Here is a small snippett to demonstrate how easy it is and the nice thing is - it completely circumvents the Outlook security patches that want you to answer whether you invoked the email at all.

    I hope this can help you - and thank yo guys for the replies.

    Public Function fnEmail(myreportname As String)
    Set objSafeItem = CreateObject("Redemption.SafeMailItem")
    ' create the Outlook session
    Set objOLook = CreateObject("Outlook.Application")
    Set objNameSpace = objOLook.GetNamespace("MAPI")
    objNameSpace.Logon
    ' create the Message
    Set objOLookMsg = objOLook.CreateItem(olMailItem)
    objSafeItem.Item = objOLookMsg


    With objSafeItem
    .To = "dale.jones2@domain.com"
    .Subject = "Anything you want"
    .Body = "Anything you want"""
    .Attachments.Add "C:\anyfile.xls"
    .Importance = 2 'High =2 low = 1
    '.Display
    .Save
    .Send
    End With


    Set objOLookMsg = Nothing
    Set objNameSpace = Nothing
    Set objOLook = Nothing
    Set objSafeItem = Nothing

    End Function
    Dale Houston, TX

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Hi Dale! Glad you found a solution. I've never used Redemption, but I know many have used it successfully. I've used ClickYes and CDO, and I've used a method to have the mail come straight out of SQL Server.
    Paul

  6. #6
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    would love to see the SQL method if you get a chance to find it. Now that is cool
    Dale Houston, TX

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I've used this for a long time:

    Our Products

    I've got a number of automated jobs running on SQL Server where I email results out to people using this. I use it to email/fax reservation confirmations, in combination with GFI Faxmaker. I guess it's obvious I like this tool.
    Paul

  8. #8
    Join Date
    Jun 2010
    Posts
    1
    DocumentBurster - http://www.pdfburst.com is a clean way to send Access reports by email. It doesn't require any kind of coding (no VBA or SQL coding) and it is easy to set up the software to send the reports by email.

  9. #9
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    Myle thank you for the collective data objects version - I will archive that one. I fin ally got the redemption to work just fine and it is very clean as well.

    Here is how I did it - make sure to download the redemption library - it is free

    Public Function fnEmail(myreportname As String, myTo As String, Optional mycc As String, _
    Optional myattachment As String, Optional Mysubject As String)
    Set objSafeItem = CreateObject("Redemption.SafeMailItem")
    ' create the Outlook session
    Set objOLook = CreateObject("Outlook.Application")
    Set objNameSpace = objOLook.GetNamespace("MAPI")
    objNameSpace.Logon
    ' create the Message
    Set objOLookMsg = objOLook.CreateItem(olMailItem)
    objSafeItem.Item = objOLookMsg


    With objSafeItem
    .To = myTo
    .Subject = myreportname
    .Body = "Anything you want"""
    .Attachments.Add myattachment
    .Importance = 2 'High =2 low = 1
    '.Display
    .Save
    .Send
    End With

    Set objOLookMsg = Nothing
    Set objNameSpace = Nothing
    Set objOLook = Nothing
    Set objSafeItem = Nothing

    End Function
    Dale Houston, TX

  10. #10
    Join Date
    Jun 2010
    Posts
    9
    I use a commercial product, Total Access Emailer, from FMS to automate sending emails from MS Access.

    It runs as an Access add-in, so no programming is required. I can setup an email blast from a wizard, specify the data source, the message, etc. It let's me reference field values in my message and/or subject. I can also attach reports as PDF files and have it filtered for each person so they only see their pages.

    More info here including a free demo: Microsoft Access Email Add-in sends messages from your Access Databases with PDF Reports, data, HTML and text

    Sure it costs money, but paid for itself many times over.

    Good luck.

  11. #11
    Join Date
    Mar 2005
    Posts
    5

    Redemption question

    I have just stumbled onto this thread after really stumbling thanks to blocked port 25. I am doing an Access database for 180 old lady golfers. All of us are volunteers, so this isn't a commercial deal at all. I have a lovely cdo routine that works great on my computer, allowing members to be kept up to date about the yearly tournament. Now, I find that the membership secretary and the tournament chairwoman use bellsouth, which is blocking outbound port 25. Neither use Outlook. Question: Will redemption work in this instance? Are there any other free alternatives?

    Thank you for any help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •