[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sending Email thru Access - without any popup dialogs

Posted on 2006-05-31
25
Medium Priority
?
270 Views
Last Modified: 2012-06-21
I am trying to send an email to my user upon completion of my updating of tables, This is an automated process that will run in the middle of the night - so dialogs opening will prevent the automation from completing.


Also How do I make sure my url will act like a Hyperlink within by message body, it is currently highlighting to the first space within the filepath.

Is there a work around for these issues.

here is the 2 different versions of  code I found @ ee:

Sample 1
Function eMail()
    Dim MailOut As Outlook.Application
    Dim eMsg As MailItem
    Set MailOut = New Outlook.Application
    Set eMsg = MailOut.CreateItem(olMailItem)
   
    With eMsg
        .To = "PFVIPTeam@dl.T-Mobile.com" & ";" & "Jason.Gonzalez@T-Mobile.com" & ";" & "Karen.Schaefer@T-Mobile.com"
        .DeleteAfterSubmit = False
        .Subject = "Manual PLANIT Forecast for Scrubbing is Completed and Ready for Your Use for the NCP"
                .Body = "The most recent PLANIT is avail for you to copy to your local drive and scrub in your Core Scenario Planning and Dimensioning." & _
                    vbCrLf & vbCrLf & "The file is located at " & _
                    vbCrLf & vbCrLf & " " & _
                    "\\Wabelhdk0215892\Intranet Information Server\CorePlan\Manual PLANIT Forecast For Scrubbing.mdb" & vbCrLf & vbCrLf & _
                    "For 'Missing Node', 'Missing Market' or 'Missing Region' issues, please email Karen Schaefer.  For trending related issues, please email or call Tanvir Rahman."
        .Send
    End With
End Function

Sample2

Function SendMessage() 'DisplayMsg As Boolean, Optional AttachmentPath)
          Dim objOutlook As Outlook.Application
          Dim objOutlookMsg As Outlook.MailItem
          Dim objOutlookRecip As Outlook.Recipient
          'Dim objOutlookAttach As Outlook.Attachment

          ' Create the Outlook session.
          Set objOutlook = CreateObject("Outlook.Application")

          ' Create the message.
          Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

          With objOutlookMsg
              ' Add the To recipient(s) to the message.
             ' Set objOutlookRecip = .Recipients.Add("PFVIPTeam@dl.T-Mobile.com")
             ' objOutlookRecip.Type = olTo
              Set objOutlookRecip = .Recipients.Add("Schaefer, Karen")
              objOutlookRecip.Type = olTo

              ' Add the CC recipient(s) to the message.
             ' Set objOutlookRecip = .Recipients.Add("Jason.Gonzalez@T-Mobile.com")
             ' objOutlookRecip.Type = olCC

               ' Set the Subject, Body, and Importance of the message.
             .Subject = "Manual PLANIT Forecast for Scrubbing is Completed and Ready for Your Use for the NCP"
            .Body = "The most recent PLANIT is avail for you to copy to your local drive and scrub in your Core Scenario Planning and Dimensioning." & _
                    vbCrLf & vbCrLf & "The file is located at " & _
                    vbCrLf & vbCrLf & " " & _
                    "\\Wabelhdk0215892\Intranet Information Server\CorePlan\Manual PLANIT Forecast For Scrubbing.mdb" & vbCrLf & vbCrLf & _
                    "For 'Missing Node', 'Missing Market' or 'Missing Region' issues, please email Karen Schaefer.  For trending related issues, please email or call Tanvir Rahman."
 
             ' Add attachments to the message.
            ' If Not IsMissing(AttachmentPath) Then
            '     Set objOutlookAttach = .Attachments.Add(AttachmentPath)
            ' End If

             ' Resolve each Recipient's name.
             For Each objOutlookRecip In .Recipients
                 objOutlookRecip.Resolve
             Next

             ' Should we display the message before sending?
             If DisplayMsg Then
                 .Display
             Else
                 .Save
                 .Send
             End If
          End With
          Set objOutlook = Nothing
      End Function

Any suggestions are greatly appreciated.

Thanks,

Karen
0
Comment
Question by:Karen Schaefer
  • 8
  • 8
  • 7
  • +1
25 Comments
 
LVL 17

Expert Comment

by:Arji
ID: 16800570
The only solution I have seen to this issue is using a commercial product like FMS's Total Access Emailer.  There are a few other products out there that are also appropriate but this is the only one I can think of at this time.
0
 
LVL 17

Expert Comment

by:Arji
ID: 16800600
The FMS product doesn't use Outlook to send email. It uses the SMTP server directly which allows you to send mail without those stupid messages.  The other products work the same way.  FMS is a little expensive but they make great products.  There may even be a freebie out there somewhere.  You would think Microsoft provided a method for us programmers to avoid these irritating messages but since when did Microsoft care?
0
 

Author Comment

by:Karen Schaefer
ID: 16800624
Thanks - there is no other workaround -

Also what about the hyperlink issue?

Karen
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:mattfmiller
ID: 16801069
Actually the way to bypass the popups is to lower certain security settings.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16801282
Karen, I remember giving u a CDO solution, did u try that? it uses smtp server directly

http://www.experts-exchange.com/Databases/MS_Access/Q_21812117.html

0
 

Author Comment

by:Karen Schaefer
ID: 16801396
I didn't understand how to setup the smtp server info etc.

Where do I find this info especially since I am not the admin for my machine.

Karen
0
 

Author Comment

by:Karen Schaefer
ID: 16801404
How do I lower the security on my machine when I am not the admin user.

Karen
0
 

Author Comment

by:Karen Schaefer
ID: 16801457
My client does not use a SMTP server only Exchange server - is there any work around.

Karen
0
 
LVL 17

Expert Comment

by:Arji
ID: 16801463
Karen,

That's seems to be a problem with spaces in your server name.  I seem to recall a question here on EE about that last year sometime.  Have you tried searching the EE database for 'Inserting hyperlinks into an email'?

Here's one solution:
http://www.experts-exchange.com/Programming/Q_21731886.html?query=Inserting+a+hyperlink+into+an+email&clearTAFilter=true

I think all you need to do in prepend 'file:' to the link:

"file:\\Wabelhdk0215892\Intranet Information Server\CorePlan\Manual PLANIT Forecast For Scrubbing.mdb"

I got a lot of other hits on the search so you may want to look at those too.
0
 
LVL 17

Expert Comment

by:Arji
ID: 16801477
BTW, lowering security has never worked for me.  Maybe mattfmiller can explain exactly what settings he was referring to.
0
 
LVL 17

Expert Comment

by:Arji
ID: 16801527
I am not sure about using the SMTP server with Exchange.  As rockiroads suggested, it will probably involve using CDO to talk to your Exchange SMPT server.  I also think that some of these products supply a simple SMTP server with their products.  Check with FMS at FMSINC.com about their product.
0
 
LVL 17

Expert Comment

by:Arji
ID: 16801586
Sorry, this should work (I think) :)

"<a href='file://Wabelhdk0215892\Intranet Information Server\CorePlan\Manual PLANIT Forecast For Scrubbing.mdb'</a>"
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16801636
CDO is shipped with Exchange so I thought it would work
http://www.microsoft.com/technet/prodtechnol/exchange/55/deploy/cdofinal.mspx
0
 
LVL 1

Expert Comment

by:mattfmiller
ID: 16801726
I don't remember exactly I think it was lowering the security concerning either VM or scripting.  
0
 

Author Comment

by:Karen Schaefer
ID: 16801857
the hyper link does not appear in the message - what am I doing wrong.  I tried it with both variable and the actual link the message generates but without the link.

Thanks

Karen.

Function SendMessage() 'DisplayMsg As Boolean, Optional AttachmentPath)
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    'Dim objOutlookAttach As Outlook.Attachment
    Dim strLtrContent, strLtrContentEnd, strHyperLink

    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")
   
    ' Create the message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
   
    With objOutlookMsg
    ' Add the To recipient(s) to the message.
    ' Set objOutlookRecip = .Recipients.Add("clientname
    ' objOutlookRecip.Type = olTo
    Set objOutlookRecip = .Recipients.Add("my name")
    objOutlookRecip.Type = olTo
   
    ' Add the CC recipient(s) to the message.
    ' Set objOutlookRecip = .Recipients.Add("ccname")
    ' objOutlookRecip.Type = olCC


    strLtrContent = "The most recent PLANIT is avail for you to copy to your local drive and scrub in your Core Scenario Planning and Dimensioning." & _
                    vbCrLf & vbCrLf & "The file is located at " & _
                    vbCrLf & vbCrLf & " "
    strLtrContentEnd = "For 'Missing Node', 'Missing Market' or 'Missing Region' issues, please email Karen Schaefer.  For trending related issues, please email or call Tanvir Rahman."
   
    strHyperLink = "Wabelhdk0215892\Intranet Information Server\CorePlan\Manual PLANIT Forecast For Scrubbing.mdb"
   
                     ' Set the Subject, Body, and Importance of the message.
        .Subject = "TEST - Manual PLANIT Forecast for Scrubbing is Completed and Ready for Your Use for the NCP"
        .BodyFormat = olFormatHTML
        .HTMLBody = strLtrContent & "<br>" _
                    & "<br>" & "<a href='file://Wabelhdk0215892\Intranet Information Server\CorePlan\Manual PLANIT Forecast For Scrubbing.mdb'</a>" _
                    & "<br>" & strLtrContentEnd & ""
    .Send
    End With
           
         Set objOutlook = Nothing
End Function
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16802137
when u send html formatted emails, do u need to specify <HTML>

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16802221
try adding this into your content

.HTMLBody = "<HTML><HEAD><META http-equiv=Content-Type content=" & chr$(34) & "text/html; charset=iso-8859.1" & chr$(34) & "></HEAD>" & vbcrlf & _
"<BODY>" & strLtrContent  & vbcrlf & vbcrlf & "<A HREF=" & chr$(34) & strHyperLink  & chr$(34) & "The Database</BODY></HTML>"



0
 

Author Comment

by:Karen Schaefer
ID: 16802292
Thanks for the suggestion however, this is the current results I get:

The most recent PLANIT is avail for you to copy to your local drive and scrub in your Core Scenario Planning and Dimensioning. The file is located at
For 'Missing Node', 'Missing Market' or 'Missing Region' issues, please email Karen Schaefer. For trending related issues, please email or call Tanvir Rahman.

Revised code:
 strLtrContent = "The most recent PLANIT is avail for you to copy to your local drive and scrub in your Core Scenario Planning and Dimensioning." & _
                    vbCrLf & vbCrLf & "The file is located at " & _
                    vbCrLf & vbCrLf & " "
    strLtrContentEnd = "For 'Missing Node', 'Missing Market' or 'Missing Region' issues, please email Karen Schaefer.  For trending related issues, please email or call Tanvir Rahman."
   
    strHyperLink = "Wabelhdk0215892\Intranet Information Server\CorePlan\Manual PLANIT Forecast For Scrubbing.mdb"
   
                     ' Set the Subject, Body, and Importance of the message.
        .Subject = "TEST - Manual PLANIT Forecast for Scrubbing is Completed and Ready for Your Use for the NCP"
        .BodyFormat = olFormatHTML
        .HTMLBody = "<HTML><HEAD><META http-equiv=Content-Type content=" & Chr$(34) & "text/html; charset=iso-8859.1" & Chr$(34) & "></HEAD>" & vbCrLf & _
                    "<BODY>" & strLtrContent & vbCrLf & vbCrLf & "<A HREF=" & Chr$(34) & strHyperLink & Chr$(34) & "The Database</BODY></HTML>" & "<br>" & strLtrContentEnd & ""
 

Thanks,

Karen
0
 
LVL 17

Assisted Solution

by:Arji
Arji earned 800 total points
ID: 16802339
That's the .BodyFormat property which she set to olFormatHTML

I would try the EE database search for further information.  It may just be the apostrophes but I'm not quite sure.    Also, is it possible you need to close the first tag?

"<a> href=file://Wabelhdk0215892\Intranet Information Server\CorePlan\Manual PLANIT Forecast For Scrubbing.mdb</a>"
     ^ - right here

However, when I manually inserted a hyperlink into a Word document and then viewed the resultant HTML code, I got something like this with my file path:

file:///\\Wabelhdk0215892\Intranet Information Server\CorePlan\Manual PLANIT Forecast For Scrubbing.mdb

You can do that by hovering over your inserted hyperlink and Word will show you the HTML code.

When I manually inserted a hyperlink to a file in an email I get this(with my file path):

file:\\Wabelhdk0215892\Intranet Information Server\CorePlan\Manual PLANIT Forecast For Scrubbing.mdb

This last one may be the only thing you need to do.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16802419
so does the html come out right now?



reharding file link, is this to be downloaded from the network then

change strHyperlink to be

strHyperLink = "file://\\Wabelhdk0215892\Intranet Information Server\CorePlan\Manual PLANIT Forecast For Scrubbing.mdb"




0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16802447
typo, forgot > on end of A HREF


.HTMLBody = "<HTML><HEAD><META http-equiv=Content-Type content=" & Chr$(34) & "text/html; charset=iso-8859.1" & Chr$(34) & "></HEAD>" & vbCrLf & _
                    "<BODY>" & strLtrContent & vbCrLf & vbCrLf & "<A HREF=" & Chr$(34) & strHyperLink & Chr$(34) & ">The Database</BODY></HTML>" & "<br>" & strLtrContentEnd & ""


now try with new strHyperlink
0
 

Author Comment

by:Karen Schaefer
ID: 16802641
After see the results of your suggestions, I get an active hyperlink under the text of the strLtrContentEnd and not displaying the actual link, how do I modify this above code to display the actual linke.

Karen
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16802669
ok, replace the text The Database

I put that down as a replacement for the link - its just display

.HTMLBody = "<HTML><HEAD><META http-equiv=Content-Type content=" & Chr$(34) & "text/html; charset=iso-8859.1" & Chr$(34) & "></HEAD>" & vbCrLf & _
                    "<BODY>" & strLtrContent & vbCrLf & vbCrLf & "<A HREF=" & Chr$(34) & strHyperLink & Chr$(34) & ">" & strHyperLink & "</BODY></HTML>" & "<br>" & strLtrContentEnd & ""

0
 

Author Comment

by:Karen Schaefer
ID: 16802735
Getting pretty close now - Do I need to close the hyperlink so that the rest of the strLtrContentEnd is not underlined and highlighted.  If so what is the proper syntax?

karen
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 1200 total points
ID: 16802809
dum dum that I am, I forgot the closing tag  we need </A> (see before </BODY>
also strlrcontentend in wrong place




.HTMLBody = "<HTML><HEAD><META http-equiv=Content-Type content=" & Chr$(34) & "text/html; charset=iso-8859.1" & Chr$(34) & "></HEAD>" & vbCrLf & _
                    "<BODY>" & strLtrContent & "<A HREF=" & Chr$(34) & strHyperLink & Chr$(34) & ">" & strHyperLink & "</A><br><p>" & strLtrContentEnd & "</BODY></HTML>"
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question