0

I developed a VBA program to send email from an Office365 email account using CDO code.

The mail is triggered by the workbook.close event.

It requires an active internet connection. In the case of no internet connection it throws an error message.

If the internet connection is not available, the workbook has to closed and the composed mail has to be stored in the outbox/draft or any other way possible in Office365 account, to be sent once the internet connection becomes available.

Sub send_mails1()

Dim objMessage, objConfig, Fields

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set objMessage = CreateObject("CDO.Message")
Set objConfig = CreateObject("CDO.Configuration")
Set Fields = objConfig.Fields
Set wb = ThisWorkbook
Set ws01 = wb.worksheets("DB_1")

datedifferance = ws01.Cells(Rows.Count, 10).End(xlUp).Row

With Fields
  .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.office365.com"
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
  .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "from@domain.com"
  .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxxx"
  '.Item("http://schemas.microsoft.com/cdo/configuration/sendtls") = True
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
  .Update
End With

Set objMessage.Configuration = objConfig

With objMessage
  .Fields("urn:schemas:httpmail:importance") = 2
  .Fields.Update
  .Subject = "NPD Weekly Status-Notification"
  .From = "from@domain.com"
  .To = "to@domain.com"
  .HTMLBody = "<font face=Calibri> <p style=font-size:12pt>" & _
                "Dear Sir,<br/>" & _
                "<br />" & _
                "Please find the deviation in Plan vs Actual date,<br/>" & _
                "<font face=Calibri> <p style=font-size:12pt>" & _
                "Note:Please refer the attached log file(s) for more information. <br/>" & vbNewLine & _
                "*This is system generated mail. Do not reply.<br/>"
    If ws1mailstatus = True Then
        .AddAttachment filename0
        Kill (filename0)
    End If
    If ws2mailstatus = True Then
        .AddAttachment filename1
        Kill (filename1)
    End If
    If ws3mailstatus = True Then
        .AddAttachment filename2
        Kill (filename2)
    End If
    If ws4mailstatus = True Then
        .AddAttachment filename3
        Kill (filename3)
    End If
    If ws5mailstatus = True Then
        .AddAttachment filename4
        Kill (filename4)
    End If
End With

On Error Resume Next
objMessage.Send
    If Err.Number <> 0 Then
        msgbox ("Error!")
    End If
On Error GoTo 0

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
Sandy
  • 87
  • 10
  • See https://learn.microsoft.com/en-us/windows/win32/api/wininet/nf-wininet-internetgetconnectedstate. Use a `do - loop`. – user14797724 Mar 22 '21 at 05:20
  • I would not recommend `Do` loop. Use `AppliCation.OnTime` to check if there is an internet connection and then send the email. You can set up a time interval after how many minutes, it should check for the internet connection. [Checking for Internet Connection](https://stackoverflow.com/questions/551613/check-for-active-internet-connection) and [Application.OnTime](https://stackoverflow.com/questions/17301512/how-to-use-application-ontime-to-call-a-macro-at-a-set-time-everyday-without-ha) – Siddharth Rout Mar 22 '21 at 05:35
  • @SiddharthRout Thank you. The mail is triggered on workbook.close event, if internet connection is not available the workbook simply closes and I believe the mail wont be saved in outbox to be sent again. – Sandy Mar 22 '21 at 09:47
  • So if you do not want to close the workbook before the email is sent then you can set `Cancel=True`? and use `Application.OnTime` to again check for the connectivity. – Siddharth Rout Mar 22 '21 at 10:23
  • Sorry for not able to convey correctly. I want to close the workbook, even if the internet connection is not available. But, the mail has to be stored in the outbox or draft and sent once the internet connection became available. – Sandy Mar 22 '21 at 11:08

1 Answers1

1

Just a quick solution, the wininet.dll is required for that one. I think it should be available on most windows systems with office installed.

Private Declare Function InternetGetConnectedState Lib "wininet.dll" _
  (ByRef dwflags As Long, ByVal dwReserved As Long) As Long

Function GetInternetConnectedState() As Boolean
  GetInternetConnectedState = InternetGetConnectedState(0&, 0&)
End Function

Then you can call it for an do until loop

Sub loop_until_connection()
Do
    Application.Wait (5000)
Loop Until GetInternetConnectedState = True

It will pause for 5 seconds and try again to get a connection.

This is not tested so i hope it works in your case.

snenson
  • 431
  • 1
  • 4
  • 12
  • thank you for the answer. But i dont want the vba code to wait for the internet connection. I want to close the workbook, even if the internet connection is not available. But, the composed mail has to be stored in the outbox or draft and sent once the internet connection became available. Is it possible – Sandy Mar 22 '21 at 11:15
  • I dont think thats possible. You could use outlook, if you send it there i think it would give you an error but it would store the mails in the outgoing section and send it when your connection is back. – snenson Mar 22 '21 at 11:23
  • i cannot use outlook because all the users do not have outlook application installed in their system. So office365 is the best option – Sandy Mar 22 '21 at 12:26
  • But where do you think its gonna be stored? You cant reach the microsoft site if you are not connected. I dont know if there is a solution for the problem you are describing but i cant imagine how it could work, sorry. – snenson Mar 22 '21 at 13:09
  • I know its a unique requirement. Thank you for your time. – Sandy Mar 23 '21 at 03:37