Snasui Excel Forum

Code: Select all

Sub SendReminder()
Dim outLookApp As Object
Dim outLookMailItem As Object
Dim iCounter As Integer
Dim MailDest As String

Set outLookApp = CreateItem("outlook.application")
Set outLookMailItem = outLookApp.CreateItem(0)

With outLookMailItem
MailDest = ""
For iCounter = 1 To WorksheetFunction.CountA(Columns(6))
If MailDest = "" And Cells(iCounter, 6).offest(0, -1) = "Send Remider" Then MailDest = Cells(iCounter, 6).Value
ElseIf MailDest <> "" And Cells(iCounter, 6).Offset(0, -1) = "Send Reminder" Then
MailDest = MailDest & ";" & Cells(iCounter, 6).Value
End If
Next iCounter

.BCC = MailDest
.Subject = "Hi"
.Body = "Reminder: Pleas sent the next progress.Please ignore if already send."
.send
End With

    
Set outLookMailItem = Nothing
Set outLookApp = Nothing
End Sub

Code: Select all

Private Sub CommandButton1_Click()
For Each Cell In Range("F2:F52")
If Cell.Value < Data + 3 And Cell.Value <> "" Then
Cell.Interior.ColorIndex = 3
Cell.Font.ColorIndex = 2
Cell.Font.Bold = True
Application.Speech.Speak ("send reminder to")
'cell.Value=cell.offset(0'-1).Value
Application.Speech.Speak (Cell.Offset(0, -1).Value)
End If

'Application.Speech.Speak ("cell.text")

Next
'SendReminderMail
End Sub

Code: Select all

Sub send_email()
Dim olApp As Object
Dim olMailItm As Object
Dim iCounter As Integer
Dim Dest As Variant
Dim SDest As String
' Subject
strSubj = "Plese send the next project"
On Error GoTo dbg
' Create a new Outlook object
Set olApp = CreateObject("Outlook.Application")
For iCounter = 9 To WorksheetFunction.CountA(Columns(9))
' Create a new item (email) in Outlook
Set olMailItm = olApp.CreateItem(0)
strBody = ""
useremail = Cells(iCounter, 9).Value
FullUsername = Cells(iCounter, 2).Value
Status = Cells(iCounter, 8).Value
pwdchange = Cells(iCounter, 3).Value
'Make the body of an email
strBody = "Dear " & FullUsername & vbCrLf
strBody = strBody & "The date and time of the last password change is" & pwdchange & vbCrLf
olMailItm.To = useremail
olMailItm.Subject = strSubj
olMailItm.BodyFormat = 1
' 1 – text format of an email, 2 -  HTML format
olMailItm.Body = strBody
olMailItm.send
Set olMailItm = Nothing
Next iCounter
Set olApp = Nothing
dbg:
'Display errors, if any
If Err.Description <> "" Then MsgBox Err.Description
End Sub