UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Sending Email From Access, Access 2016    
 
   
payfast8898
post Oct 31 2019, 09:40 AM
Post#1



Posts: 413
Joined: 23-April 15
From: NY


2 Questions
Can I pass from to MS outlook from MS Access when sending a email though outlook from Ms access? One of our offices have shifts of girls who use the same computer sometimes they don't log off correctly or need to borrow another station etc but log into our software with their id so when they send an email using outlook it puts who ever is logged on to the computers email as default. we mostly use the smtp email and that works perfectly with .from but we have been having some lag issues with it and other stuff so the company wants to go through outlook.

Question two is how do we minimize outlook on open from ms access. I have to open the shell ( Shell "Outlook.exe") to make sure they don't get stuck into the outbox folder and have it set to send and receive before closing and send and receive every minute. I have it set so if it is already open it will not open the shell so I they only have to deal with it one time, but I was just curious how I can do that on the first time it opens, I have been tying to find something but not much luck hoping someone has a quick fix and thanks.

--------------------
"When a collection of Brilliant minds, hearts and talents come together...
Expect a masterpiece"
Go to the top of the page
 
projecttoday
post Oct 31 2019, 09:51 AM
Post#2


UtterAccess VIP
Posts: 12,222
Joined: 10-February 04
From: South Charleston, WV


Send an email.

--------------------
Robert Crouser
Go to the top of the page
 
payfast8898
post Oct 31 2019, 10:25 AM
Post#3



Posts: 413
Joined: 23-April 15
From: NY


Sorry that link doesn't answer either question? I have been emailing for over 15 years but I have not run across the questions I have asked. Thank you for taking the time to respond though never hurts to check.

--------------------
"When a collection of Brilliant minds, hearts and talents come together...
Expect a masterpiece"
Go to the top of the page
 
cheekybuddha
post Oct 31 2019, 10:39 AM
Post#4


UtterAccess Moderator
Posts: 12,818
Joined: 6-December 03
From: Telegraph Hill


Rough code:
CODE
' ...
  Dim objOutlook As Object

  On Error Resume Next
  Set objOutlook = GetObject(, "Outlook.Application")
  If Err Then
    Err.Clear
    Set objOutlook = CreateObject("Outlook.Application")
  End If
  On Error Goto 0

  objOutlook.Visible = False      ' <-- Prevents Outlook from being displayed

' ...


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
payfast8898
post Oct 31 2019, 11:23 AM
Post#5



Posts: 413
Joined: 23-April 15
From: NY


says it don't support this property or method objOutlook.Visible = False

--------------------
"When a collection of Brilliant minds, hearts and talents come together...
Expect a masterpiece"
Go to the top of the page
 
cheekybuddha
post Oct 31 2019, 11:57 AM
Post#6


UtterAccess Moderator
Posts: 12,818
Joined: 6-December 03
From: Telegraph Hill


It appears that Outlook works the other way round from other Office apps, and you actually have to call .Display on the objects you want to see.

So just remove that line, and you should be able to work with Outlook hidden.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
DanielPineault
post Oct 31 2019, 12:20 PM
Post#7


UtterAccess VIP
Posts: 7,346
Joined: 30-June 11



From what I remember, unlike most other Applications, you cannot hide Outlook using .Visible = False.

You shouldn't use Shell, but rather CreateObject or GetObject that way you'll have an object variable so you can automate Outlook as you need. The link provided earlier shows this.
CODE
    Dim oOutlook              As Object    'Outlook.Application

    On Error Resume Next
    Set oOutlook = GetObject(, "Outlook.Application")        'Bind to existing instance of Outlook
    If Err.Number <> 0 Then        'Could not get instance, so create a new one
        Err.Clear
        Set oOutlook = CreateObject("Outlook.Application")
    End If
    On Error GoTo Error_Handler


For instance, once you have an Outlook object variable, you can minimize it by using 1 line of code
CODE
oOutlook.ActiveWindow.WindowState = 1

0 = Maximized (olMaximized), 1 = Minimized (olMinimized), 2 = Normal (olNormalWindow)

I'm still not clear about your 1st question though.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
isladogs
post Oct 31 2019, 12:50 PM
Post#8


UtterAccess VIP
Posts: 2,312
Joined: 4-June 18
From: Somerset, UK


I don't know whether its of any use here but many years ago before I started using CDO to send email directly from Access, I used the following two procedures to send email via Outlook
The first does so without Outlook being displayed.

CODE
Public Function SendEmailUsingOutlook(strSendTo As String, strCopyTo As String, strSubject As String, strMessage As String, strAttachment As String)

On Error GoTo ErrHandler

'CR v5161 - Uses late binding to send email without displaying Outlook

Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim StartOutlookFlag As Boolean

StartOutlookFlag = False

' Create the Outlook session.
If IsAppRunning("Outlook.Application") = True Then
    'Use existing instance of Outlook
    Set objOutlook = CreateObject("Outlook.Application")
Else
   'Could not get instance of Outlook, so create a new one
        Path = GetAppExePath("outlook.exe")    'determine outlook's installation path
        Shell (Path), vbMinimizedFocus   'start outlook
        Do While Not IsAppRunning("Outlook.Application")
            DoEvents
        Loop
        Set objOutlook = GetObject(, "Outlook.Application") 'Bind to new instance of Outlook
        StartOutlookFlag = True 'needed so Outlook can be closed later
End If

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

' Add the To/Subject/Body/Attachments to the message then send the message
With objOutlookMsg
    .To = strSendTo
    .CC = strCopyTo
    .Subject = strSubject
    .Body = strMessage
    If Nz(strAttachment, "") <> "" Then
        .Attachments.Add strAttachment
    End If
   ' .Display 'disabled - do not display message
    .Save
    .Send
End With

Set objOutlook = Nothing
Set objOutlookMsg = Nothing

'close Outlook if it was opened for this function - time may need modifiying
DoEvents
DoEvents
Wait 5 'allow time to send message
If StartOutlookFlag = True Then CloseOutlook

ErrHandlerExit:
   Exit Function

ErrHandler:
   If Err.Number <> 287 Then 'And err.Number <> 429 Then
        MsgBox "Error " & Err.Number & " in SendEMailUsingOutlook routine: " & Err.Description
   End If
   Resume ErrHandlerExit

End Function


The second very similar function opens Outlook before sending the email
CODE
Public Function SendEmailDisplayOutlook(strSendTo As String, strCopyTo As String, strSubject As String, strMessage As String, strAttachment As String)

On Error GoTo ErrHandler

'Uses late binding to send email(so that a reference to Outlook library is not needed)

Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim sAPPPath        As String

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

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(0)
' Add the To/Subject/Body/Attachments to the message then display the message for editing
With objOutlookMsg
    .To = strSendTo
    .CC = strCopyTo
    .Subject = strSubject
    .Body = strMessage
    If Nz(strAttachment, "") <> "" Then
        .Attachments.Add strAttachment
    End If
    .display
    '.Send
End With

Set objOutlook = Nothing
Set objOutlookMsg = Nothing

ErrHandlerExit:
   Exit Function

ErrHandler:
   MsgBox "Error " & Err.Number & " in SendEMailDisplayOutlook routine: " & Err.Description
   Resume ErrHandlerExit

End Function


NOTE: The first function makes use of Daniel's IsAppRunning function
I hope its OK for me to post it here

CODE
'---------------------------------------------------------------------------------------
' Procedure : IsAppRunning
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine is an App is running or not
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sApp      : GetObject Application to verify if it is running or not
'
' Usage:
' ~~~~~~
' IsAppRunning("Outlook.Application")
' IsAppRunning("Excel.Application")
' IsAppRunning("Word.Application")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' ********************************************************************************
******
' 1         2014-Oct-31                 Initial Release
'---------------------------------------------------------------------------------------
Function IsAppRunning(sApp As String) As Boolean
    On Error GoTo Error_Handler
    
    Dim oApp As Object

    Set oApp = GetObject(, sApp)
    IsAppRunning = True

Error_Handler_Exit:
    On Error Resume Next
    Set oApp = Nothing
    Exit Function

Error_Handler:
    Resume Error_Handler_Exit
End Function



--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
payfast8898
post Oct 31 2019, 02:45 PM
Post#9



Posts: 413
Joined: 23-April 15
From: NY


Daniel and isladogs maybe I'm not asking this correctly. I have pretty much the same codes as you have to send emails all three ways. I already have a email popup in outlook to send a email so they can read it before sending. works great no issues, however, I have tested both your codes and they do the same thing, if the program (shell) Outlook is not running after I hit send the email will stay in the outbox until someone opens the shell.

this is what I use to see if it is open and if not open it.

CODE
On Error GoTo Err_Command50_Click

Dim obj As Object
Set obj = GetObject(, "Outlook.Application")
Exit_Command50_Click:
    Exit Sub

Err_Command50_Click:
    Shell "Outlook.exe"
    
    Resume Exit_Command50_Click


if outlook is closed outlook shell will open. I already have all the other code to you guys use to send emails and open and email to send .display if I want it to send automatically I can use the .send I have all that down.

I have to use .display for this company so they can double check to see what goes out, (that is what they want) however, if outlook shell is not running the emails will not get sent out they stay in the outbox same as both example above.
So I open Outlooks shell and it is maximized the first time which is no big deal I'm just trying to make things a little easier so they don't have to hit minimize the first time using it.

The codes above just open an email window within outlook not the actual program if that makes sense. I'm just looking to open the Shell "Outlook.exe" and Outlook.ActiveWindow.WindowState = 1 somehow. And it could be me being dumb but if you have an example that can do it please let me see it I would greatly appreciate it.

Daniel my first question is you have a From, To, CC. Bcc I'm just looking to open outlook and change the from as I would the to from my access form



--------------------
"When a collection of Brilliant minds, hearts and talents come together...
Expect a masterpiece"
Go to the top of the page
 
DanielPineault
post Oct 31 2019, 03:04 PM
Post#10


UtterAccess VIP
Posts: 7,346
Joined: 30-June 11



Shell won't give you an object to automate.

You would be best to use https://www.devhut.net/2014/10/31/createobj...-work-now-what/

As for change the from, this can be an issue, because many servers will block email whose FROM doesn't match the actual outgoing account. Regardless, you can easily change the from by doing something like
CODE
Set oOutlookMsg = oOutlook.CreateItem(olMailItem)
oOutlookMsg.SentOnBehalfOfName = "someone@somewhere.com"




A much better approach is to create multiple accounts and switch the account used see https://www.devhut.net/2010/09/03/vba-send-...ook-automation/ (look at the last input variable (sAccount) and the way it is used in the code).

Another solution would be CDO. Store each users info in the db (I'd encrypt it personally) and then depending on who is logged in use their info for connecting and sending using CDO.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
isladogs
post Oct 31 2019, 03:35 PM
Post#11


UtterAccess VIP
Posts: 2,312
Joined: 4-June 18
From: Somerset, UK


Neither of the functions I posted actually send the email. They just set it up ready for sending.
If you want I can post the code I use that actually sends the mail without displaying Outlook.

But as I previously mentioned, I also now use CDO to send email from Access. It works with both plain text and HTML email
Advantages include not needing a separate application to send mail.
Disadvantages include not having a copy in the users' sent folder...unless the app send a copy to the user as a user option

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
payfast8898
post Oct 31 2019, 03:51 PM
Post#12



Posts: 413
Joined: 23-April 15
From: NY


ok on the from SentOnBehalfOfName I can look at that and I've been sending Mail CDO SMTP for 10 years at least and I know how to send emails though outlook with out it opening isladogs as I said I need it to open so they can view what they are sending but I was just looking for ms outlook to minimize in the back ground after it pops up or it will sit in the outbox folder. now if you can make that happen that is what I am looking for.

--------------------
"When a collection of Brilliant minds, hearts and talents come together...
Expect a masterpiece"
Go to the top of the page
 
isladogs
post Oct 31 2019, 05:38 PM
Post#13


UtterAccess VIP
Posts: 2,312
Joined: 4-June 18
From: Somerset, UK


Sorry but I'm not clear about what you asking for.

With respect to CDO, I have an Access form used to create new emails and use the settings to send the mail including any attachments

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
payfast8898
post Oct 31 2019, 11:41 PM
Post#14



Posts: 413
Joined: 23-April 15
From: NY


I already use the CDO with attachments and signatures and I have it update to a history table to keep track of out going emails.
What I was looking for is an output to Outlook, this is the code I use below to send a email. works perfectly, however, when it opens a email in out filled out and ready to go my clients have to inspect it again to make sure it is ok, silly I know but is their procedure, they double check it and then hit send. The window closes.
Now if MS outlook the program is not open the email will stay in the outbox until someone opens outlook. obviously the employees should know to do this however they don't go figure. so I use the code to check to see if outlook is open, if not I open it with code. the window pops up in front of the screen like a popup. the issues are: one someone might hit the x to get it out of the way thus the email may not go out, the other it can be a little annoying. So all I want is a way when outlook the program opens that it can be minimized. I have tried a lot of things just not in my wheel house so I was hoping someone would know how to help.

CODE
Public Function emailoutlookx()

On Error GoTo ErrorHandler

Dim objOutlook As Object 'Outlook.Application
Dim objMailItem As Object 'Outlook.MailItem
Dim EmailAddress As String



Set objOutlook = CreateObject("Outlook.Application")
Set objMailItem = objOutlook.CreateItem(0)

DoCmd.RunCommand acCmdSaveRecord
'EmailAddress = "payfast@payfastway.com" 'Me!email"
With objMailItem

.to = [to]
.Subject = [subject]
.cc = [cc]
.bcc=[bcc]
.htmlbody = "<div>" & [body] & "</div><div><br><br><div>" & IIf([Sig] = True, [signature], "") & " </div > "
.Attachments.add (Attch1)
.Display
'.send
End With


Exit Function
ErrorHandler:
MsgBox "Error Number: " & err.number & " " & err.Description

End Function

--------------------
"When a collection of Brilliant minds, hearts and talents come together...
Expect a masterpiece"
Go to the top of the page
 
cheekybuddha
post Nov 1 2019, 08:06 AM
Post#15


UtterAccess Moderator
Posts: 12,818
Joined: 6-December 03
From: Telegraph Hill


What happened when you tried Daniel's suggestion in Post#7 ?

--------------------


Regards,

David Marten
Go to the top of the page
 
payfast8898
post Nov 1 2019, 09:19 AM
Post#16



Posts: 413
Joined: 23-April 15
From: NY


I couldn't get it to work.
I found another post where

CODE
Dim RetVal
RetVal = Shell("Outlook.EXE", 6)


vbHide 0 Window is hidden and focus is passed to the hidden window. The vbHide constant is not applicable on Macintosh platforms.
vbNormalFocus 1 Window has focus and is restored to its original size and position.
vbMinimizedFocus 2 Window is displayed as an icon with focus.
vbMaximizedFocus 3 Window is maximized with focus.
vbNormalNoFocus 4 Window is restored to its most recent size and position. The currently active window remains active.
vbMinimizedNoFocus 6 Window is displayed as an icon. The currently active window remains active.

if I use 3 it does maximize it and the other ones open it in pretty much the restored position or size anyways. I have tried all the others and will not minimize it so not sure.



--------------------
"When a collection of Brilliant minds, hearts and talents come together...
Expect a masterpiece"
Go to the top of the page
 
cheekybuddha
post Nov 1 2019, 09:40 AM
Post#17


UtterAccess Moderator
Posts: 12,818
Joined: 6-December 03
From: Telegraph Hill


Outlook often behaves weirdly/differently (by design), mainly due to the need to prevent abuse/spamming.

I'm afraid I'm out of suggestions. frown.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    2nd June 2020 - 06:44 AM