How to use excel to send email automaically to me user

[ZOL]

hi, how do i send email to the users using mt excel file automatically? i have the code i get from the net. but i dunno how to use. they gave me an error when it is at .send. do i have to configure anything? i jus paste the code in my excel. please help, i am new to vba. thanks

Sub Sendmail()
     
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    sTo = "[EMAIL="whcmelvin@email.com"]whcmelvin@email.com[/EMAIL]"
    sCC = ""
    sBCC = ""
    sSubject = "Overdue"
    strbody = "Good Morning" & vbNewLine & vbNewLine & _
    "Here is a message"
    With OutMail
        .To = sTo
        .CC = sCC
        .BCC = sBCC
        .Subject = sSubject
        .Body = strbody
        .Send
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
 
Sub CheckDue()
    Dim rDates As Range, cl As Range
     
    Set rDates = Sheet1.Range("a1", Range("a65536").End(xlUp))
     
    For Each cl In rDates
        If cl.Value >= Date Then
            sTo = cl.Offset(0, 1).Value 'assumes addresses in Column B
            Call Sendmail
        End If
         
    Next cl
     
     
End Sub

 

[ZOL]

Any idea anyone?

 

[VBACO]

Looks like you need to open outlook first and make sure you have outlook referenced. This code works for me.

Sub Sendmail()
Dim OutApp As New Outlook.Application
Dim OutMail As Outlook.MailItem
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    sTo = "EMAIL ADDRESS"
    sCC = ""
    sBCC = ""
    sSubject = "Overdue"
    strbody = "Good Morning" & vbNewLine & vbNewLine & _
    "Here is a message"
    
    OutOpen = True
    Set myExplorer = OutApp.ActiveExplorer
    If TypeName(myExplorer) = "Nothing" Then
        OutOpen = False
        Set myNameSp = OutApp.GetNamespace("MAPI")
        Set myInbox = myNameSp.GetDefaultFolder(olFolderInbox)
        Set myExplorer = myInbox.GetExplorer
    End If
    With OutMail
        .To = sTo
        .CC = sCC
        .BCC = sBCC
        .Subject = sSubject
        .Body = strbody
    End With
    OutMail.Send
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[ZOL]

is it possible to not open outlook and send the email? cos i want it to send automatically. my purpose is that when a user create an account in the excel, a confirmation email will be sent.

 

[VBACO]

If you try the code I supplied, outlook should open but shouldnt be displayed.

 

[ZOL]

do i have to conigure the outlook to an existing email first?
They gave me an error, User Define type not define.

 

[VBACO]

Not sure what you mean. Are you saying that Outlook has not been setup for the user yet?

 

[ZOL]

Ya. go i have to set it up first b4 using?

 

[VBACO]

Yes, the user will have had to have set up outlook for the email to be sent.