Trying to automate Email and Outlook

[bcarter17]

Hello,
I am trying to do a very simple thing. The company owner asked me if I could send him text updates on how many tons of rock we have shipped throughout the day. I have created a macro that uses the SendObject module with a joined query that pulls the updated tonnage. I can then set up an email using the phone number (0000000000@vtext.com) and it will create the email which in turn would go to the cell phone as a text message. However, it gets to the point where it creates the email window, but it does not send it automatically. I have to click the send button to place it into the outbox of Outlook. Is there a way the entire process can be automated without any intervention?

Thanks in advance!!

[ranman256]

is this code in Outlook? Send is the final command:

or use:

Send1Email "weCoyote@acme.com", "my Subject","the body part"

if sending from excel or access YOU MUST HAVE THE OUTLOOK REFERENCE CHECKED IN VBE; Alt-F11, menu,tools, references, Microsoft Outlook XX Object library

Public Function Send1Email(ByVal pvTo, ByVal pvSubj, ByVal pvBody, Optional ByVal pvFile) As Boolean
Dim oApp As Outlook.Application
Dim oMail As Outlook.MailItem

On Error GoTo ErrMail


'NOTE : YOU MUST HAVE THE OUTLOOK REFERENCE CHECKED IN VBE; Alt-F11, menu,tools, references, Microsoft Outlook XX Object library


Set oApp = CreateObject("Outlook.Application")  'not this


Set oMail = oApp.CreateItem(olMailItem)
With oMail
    .To = pvTo
    .Subject = pvSubj
    If Not IsMissing(pvFile) Then .Attachments.Add pvFile, olByValue, 1
    
    .HTMLBody = pvBody
    'If Not IsNull(pvBody) Then .Body = pvBody
    
    .Send          'send now
    ''.Display True   'show user but dont send yet
End With


Send1Email = True
endit:
Set oMail = Nothing
Set oApp = Nothing
Exit Function


ErrMail:
MsgBox Err.Description, vbCritical, Err
Resume endit
''DoCmd.OutputTo acOutputReport, "rMyReport", acFormatPDF, vFile
End Function

[pbaldy]

If memory serves the last argument of SendObject determines whether it sends or stays open.

[bcarter17]

So to clarify, I made an attempt at this a few years ago in a previous job but didn't have success due to too many IT loopholes. Now I am working for a small company and I am the IT guy. I have initially tried to do this in Access 2007 using a macro. I created a query that pulls the total tons for the day, then used the macro to attach the query to a SendObject command. Since it require an output format, I chose a TXT file. The macro when ran did create an email in Outlook, but ran into issues auto sending it with some type of confirmation request box asking about another application is trying to send an email, accept or deny. I manually them had to click accept.

Maybe I need to go the straight VBA route? My owner simply would like to get a text on a schedule telling him how many tons have been shipped for the day, maybe every 30 minutes to an hour. A simple text saying "Michels Project Update - 2400 tons" as an example. I am sending as an email to his phone number using phone number.vtext.com for Verizon.

Is there a VBA way to do this that is cleaner?

[Welshgasman]

That is Outlook trying to protect you.
However I have forgotten how to get past that stumbling block.

https://www.google.com/search?q=anot...hrome&ie=UTF-8

More pertinently https://learn.microsoft.com/en-us/ou...on-your-behalf

[bcarter17]

I did try using VBA instead of a Macro and it did run much better and the text message is cleaner to read, however as you mentioned, it still pops up the security question about do I want to allow an outside program to send an email. And even if I answer yes, I still have to open Outlook to have it send from the outbox. I could make sure that Outlook is already open, but it still will pause with the security question. I did follow another suggestion and check the settings to the lowest security, but it still says my antivirus is valid and I am using the lowest security settings.

[pbaldy]

I have a computer that sends out automated emails all day long, and the virus setting is what let it work for me. You could use CDO as an alternative, which bypasses Outlook:

http://www.devhut.net/vba-cdo-mail/