Vansweevelt
New Member
- Joined
- Aug 31, 2021
- Messages
- 1
- Office Version
- 2010
- Platform
- Windows
I have created a VBA code that an e-mail over GroupWise will pop-up automatically when a due date is passed (subject and email address filled in automatically).
E-mail dialog opens when closing the excel-file.
Question: is there a possibility to skip the dialog and let the VBA code directly send the mail ? So avoid opening the e-mail and manually clicking on "send" ?
Code:
Option Explicit
Sub Mail()
Dim path1 As String
Dim path2 As String
Dim name1 As String
Dim name2 As String
Dim fn As String
Dim subj As String
Dim r1 As String
Dim recip As Variant
Dim x As Integer
Dim i As Long
Dim rw As Long
Dim rws As Long
Dim d As Date
rws = Application.WorksheetFunction.CountA(Sheets("ShortTerm").Range("E5:E30"))
For i = 1 To rws
d = Format(Now(), "dd/mm/yyyy")
If Sheets("ShortTerm").Cells(i + 4, 8) <= d - 1 And Sheets("ShortTerm").Cells(i + 4, 9) = False Then
rw = i + 4
path1 = Sheets("ShortTerm").Cells(rw, 18)
name2 = Sheets("ShortTerm").Cells(rw, 19)
r1 = Sheets("ShortTerm").Cells(rw, 7)
recip = Array(r1)
subj = "To Do !! " & Sheets("ShortTerm").Cells(rw, 5)
name1 = ActiveWorkbook.Name
Application.MailLogon "Novell Default Settings"
Application.Dialogs(xlDialogSendMail).Show (recip), (subj)
End If
Next i
End Sub
E-mail dialog opens when closing the excel-file.
Question: is there a possibility to skip the dialog and let the VBA code directly send the mail ? So avoid opening the e-mail and manually clicking on "send" ?
Code:
Option Explicit
Sub Mail()
Dim path1 As String
Dim path2 As String
Dim name1 As String
Dim name2 As String
Dim fn As String
Dim subj As String
Dim r1 As String
Dim recip As Variant
Dim x As Integer
Dim i As Long
Dim rw As Long
Dim rws As Long
Dim d As Date
rws = Application.WorksheetFunction.CountA(Sheets("ShortTerm").Range("E5:E30"))
For i = 1 To rws
d = Format(Now(), "dd/mm/yyyy")
If Sheets("ShortTerm").Cells(i + 4, 8) <= d - 1 And Sheets("ShortTerm").Cells(i + 4, 9) = False Then
rw = i + 4
path1 = Sheets("ShortTerm").Cells(rw, 18)
name2 = Sheets("ShortTerm").Cells(rw, 19)
r1 = Sheets("ShortTerm").Cells(rw, 7)
recip = Array(r1)
subj = "To Do !! " & Sheets("ShortTerm").Cells(rw, 5)
name1 = ActiveWorkbook.Name
Application.MailLogon "Novell Default Settings"
Application.Dialogs(xlDialogSendMail).Show (recip), (subj)
End If
Next i
End Sub