Add Start Outlook Session To VBA Code

[tweek312]

This should be really simple for you pros but I have not been able to figure this one out.

All I need to do is to add code that will open an outlook session or use the current one.

Please dont change the code that i have already done. I know that theres probably a better way but what here works for me.

Thanks,
tW33k

Sub prep_everpt()

Dim ReturnValue As Integer
   ReturnValue = MsgBox("Are you sure you want to compile the report?", vbQuestion + vbOKCancel, "Compile Report")
   Select Case ReturnValue
   Case vbOK
      
   Case vbCancel
      Exit Sub
   End Select

Application.ScreenUpdating = False
Sheets("live_status").Copy

'convert to values
 
 Application.DisplayAlerts = False
    Range("A1:N64").Select
    Selection.Copy
    Range("A1:E1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 Application.DisplayAlerts = True

'delete charts

    ActiveSheet.ChartObjects("Chart 6").Activate
    ActiveChart.ChartArea.Select
    ActiveWindow.Visible = False
    Selection.Delete

    ActiveSheet.ChartObjects("Chart 7").Activate
    ActiveChart.ChartArea.Select
    ActiveWindow.Visible = False
    Selection.Delete

    ActiveSheet.ChartObjects("Chart 11").Activate
    ActiveChart.ChartArea.Select
    ActiveWindow.Visible = False
    Selection.Delete

'delete buttons

    ActiveSheet.Shapes("Button 8").Select
    Selection.Delete
    ActiveSheet.Shapes("Button 12").Select
    Selection.Delete
    ActiveSheet.Shapes("Button 13").Select
    Selection.Delete
    ActiveSheet.Shapes("Button 39").Select
    Selection.Delete

Application.ScreenUpdating = True

Call break_links

Range("A1").Select

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs ("Q:\live_updates\cs_email\UBER_Live_Report_" & Format(Date, "mm.dd.yy") & ".xls")
Application.DisplayAlerts = True
Application.Dialogs(xlDialogSendMail).Show arg1:="Test Dist List", arg2:="Evening Status Report"
ActiveWorkbook.Close

End Sub

 

[theniteowl]

tweek312, here is my entire code for email.
I did not write this code but do not remember where it came from originally. I have modified it a bit for my own use.

This is setup as a function but I commented out the function lines. To make it a function again just remove the comments followed by **** and set the ToAddress line = to name (as it is passed into the function.

If you cut and paste this into a file and save it with a .vbs extension it will work directly so that you can see it execute.

'****Function SendEmail(name)
  Dim ToAddress
  Dim MessageSubject
  Dim MessageBody
  Dim MessageAttachment
  Dim ol, ns, newMail
  Dim thisdate
  thisdate = Date
  Dim Pos
  Dim SenderFullName
  Dim SenderLastName
  Dim SenderFirstName
  Dim RecipientFullName
  Dim RecipientLastName
  Dim RecipientFirstName


  ToAddress = name '### If not using this as a function then set the value of ToAddress to the name to search for.


  MessageSubject = "My Test Email"
  MessageBody = "This is my test email message body"

  On Error Resume Next

  ' Assume success
  fSuccess = True

  Set ol = GetObject("", "Outlook.application")
  ' If Outlook is NOT Open, then there will be an error. 
  ' Attempt to open Outlook
  If Err.Number > 0 Then
    Err.clear
    Set ol = CreateObject("Outlook.application")
    If Err.Number > 0 Then
      MsgBox "Could not create Outlook object", vbCritical
      fSuccess = False
'****      Exit Function
    End If
  End If

  ' If we've made it this far, we have an Outlook App Object 
  ' Now, set the NameSpace object to MAPI Namespace
  Set ns = ol.GetNamespace("MAPI")
  ns.logon "","",true,false
    
  If Err.Number > 0 Then
    MsgBox "Could not create NameSpace object", vbCritical
    fSuccess = False
'****    Exit Function
  End If

  If fsuccess = True Then
    ' Create the mail item and populate it with data
    Set newMail = ol.CreateItem(olMailItem)
    newMail.Subject = MessageSubject
    SenderFullName = ns.CurrentUser.Name
    Pos = InStr(1, SenderFullName, ",", vbTextCompare)
    SenderLastName = Trim(Left(SenderFullName, Pos - 1))
    SenderFirstName = Right(SenderFullName, Len(SenderFullName) - (Pos + 1))

    ' validate the recipient.
    Set myRecipient = ns.CreateRecipient(ToAddress)
    myRecipient.Resolve

    If Not myRecipient.Resolved Then
      MsgBox "Name or ID is invalid or there is more than one person with the same first & last name." & vbCrLf & "If you entered a name, try using their ID number instead."
    Else
      resetid()
      newMail.Recipients.Add(ToAddress)
' Below line replaced because when myRecipient resolves above it converts into Last, First name and if there
' are two people with the same first and last names it does not know which to use and will error out.
' So script will verify that ID or name as entered exists on server but use the original information to send the email
' rather than the newly resolved name.  This way if there is more than one match possible you can still send the email
' by using their ID# instead of name.
'      newMail.Recipients.Add(myRecipient)
      RecipientFullName = myRecipient
      Pos = InStr(1, RecipientFullName, ",", vbTextCompare)
      RecipientLastName = Trim(Left(RecipientFullName, Pos - 1))
      RecipientFirstName = Right(RecipientFullName, Len(RecipientFullName) - (Pos + 1))
      Message = MessageBody
      newMail.HTMLBody = Message & "<br>"
'      newMail.Display 'Un-comment this line and comment out the newMail.Send line if you want to review the message before sending.
      newMail.Send  
      MsgBox "Your message has been sent."
    End If
  End If
  ' Close out our objects.
  Set ol = Nothing
  Set ns = Nothing
'****End Function

Paranoid? ME?? WHO WANTS TO KNOW????

 

[tweek312]

I think that the issue lies within our Exchange server configuration. The code that you gave me nightowl works or at least it seems to. But it still just puts the email in the outbox it never actually sends it until outlook is manually opened. Is this because I am using an exchange server and not a regular email service?

NeWayz... This stuff just gets weirder and weirder..

Thanks,

tW33k

 

[theniteowl]

I do not know how much about exchange server configuration but in our company email is sent immediately, not in timed intervals like you would see with Outlook configured solely as internet email. The moment I hit send it actually sends and drops right into the box of the recipient (assuming the recipient is someone on our exchange server).

If you type up a new message manually and hit send does it go into your Outbox or actually send immediately?

In Outlook under Tools\Services\Delivery is your top transport set to Microsoft Exchange Transport?

I do not know if that would make a difference or not.
I do not know how our Outlook handles the immediate processing of email, whether it is a client side setting or server side.


Paranoid? ME?? WHO WANTS TO KNOW????

 

[tweek312]

I just tested your therory and as expected the email I sent (which happened to be an excel spreadsheet) did not send immediately. Instead it went to the Outlook outbox just as it always has. I did notice that it eventualy sent though when an email was received by another co-worker. This leads be to believe that the processing of the email is server side.

As for these transport settings I am not sure what or where you are referring to as I am using OLK 2003. The path as you indicated does not exist on my version of OLK.

Whew!...I knew this was going to be difficult.

Thanks,

tW33k

 

[theniteowl]

I'm still stuck on Outlook 2000. Gotta get me a computer upgrade here sometime and I will get XP with Office 2003.

Not sure on your version where that setting exists.
You may be able to get to the settings from Control Panel and going into the Mail icon if it is there.
Essentially you are just going in to where the email services are configured with server and account information.

I have wondered in the past how Outlook knows that a message is posted and to send immediately. I had thought it was on the exchange server side but if so, there has to be a trigger to tell the exchange server mail is ready to be collected. So it may indeed be a client side setting.
There is a setting to tell Outlook how often to send mail/check for new mail and you can always override that setting by clicking the Send/Receive button but when left alone it will always poll at the set intervals.

The question is though, is there a programatic method to force the Send/Receive to occur when creating the mail object from code.

Paranoid? ME?? WHO WANTS TO KNOW????

 

[PHV]

What about the 2nd tab of the Outlook's menu Tools -> Options ... sheet ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[theniteowl]

Take a look here, it shows how to set Send Immediately in Outlook 2003.

http://www.peachpit.com/articles/article.asp?p=102170&seqNum=3&rl=1

Paranoid? ME?? WHO WANTS TO KNOW????

 

[tweek312]

I found nothing useful in the outlook settings except the "Offline" sending options. I suppose setting the options to 1 minute might help somewhat... but id rather not go around changing everyones email settings. Plus this probably drains resources and causes more network traffic.

I think you have the right idea nightowl, we need to find a way to trigger outlook to go online just before the email is sent and then have it close again.

The site that you pointed us to only works when outlook is actually open. Microsoft uses the the word "Connected" to indicate outlook being open.