Help with sending email via Ouloook 365 (OWA) in Excel

[rv02s]

Hi all

I have the following code in my Excel worksheet. This code successfully send out email to our customers when if the criteria is true. I have tested this at home and it works perfectly as I use Outlook at home. However when I take this code to work I couldn't get this working as at work we use Outlook 365 and we use web Outlook, OWA. Every time I run this code it tries to setup Outlook on the PC for me which we are not allowed to do.

is there a setting I can specify to use OWA as I don't think I am allowed to install outlook at work. I need this help urgently as I have to present this code to my boss.

Any help is much appreciate it.

 Private Sub CommandButton1_Click()
 Dim i As Long


 With Sheets("Sheet1")
 ' if 30 days have passed and customer has spent more than 1000 and want to receive email
 For i = 5 To .Range("A" & Rows.Count).End(xlUp).Row
 If DateDiff("d", .Cells(i, 10).Value, Now) >= 30 And .Cells(i, 11).Value = "y" And .Cells(i, 8).Value >= 1000 Then
 If DateDiff("d", .Cells(i, 9).Value, Now) >= 30 Then
 'send email using Outlook
 With CreateObject("Outlook.Application").CreateItem(0)
 .To = Sheets("Sheet1").Cells(i, 2).Value
 .CC = "ggg@hotmail.com"
 .Subject = "30 Days has passed since your last purchase."
 .Body = "Dear " & Cells(i, 1).Value & Cells(i, 2).Value & vbNewLine & vbNewLine & _
 "It has been a while since you have visited the Department ......"
 .Display ' Change to .Send to skip the display

 End With
 .Cells(i, 10).Value = Format(Now, "dd/mm/yyyy")
 End If
 ElseIf DateDiff("d", .Cells(i, 10).Value, Now) >= 15 And .Cells(i, 11).Value = "y" And .Cells(i, 8).Value >= 500 And .Cells(i, 8).Value < 1000 Then
 If DateDiff("d", .Cells(i, 9).Value, Now) >= 15 Then
 'send email using Outlook
 With CreateObject("Outlook.Application").CreateItem(0)
 .To = Sheets("Sheet1").Cells(i, 2).Value
 .CC = "ggg@hotmail.com"
 .Subject = "15 Days has passed since your last purchase."
 .Body = "Hi " & Cells(i, 1).Value & Cells(i, 2).Value & vbNewLine & vbNewLine & _
 "Come and buy more things!"
 .Display ' Change to .Send to skip the display

 End With
 .Cells(i, 10).Value = Format(Now, "dd/mm/yyyy")

 End If
 '.Cells(i, 9).Value = Format(Now, "dd/mm/yyyy")
 End If

 Next i
 End With
 End Sub

 

[Andrew Poulsom]

Outlook OWA doesn't have a VBA object model. You could try SendMail or cdo.

 

[rv02s]

Will it be possible to send me the code or example of this in my code above as I am new to vba
thanks

 

[Andrew Poulsom]

Here are a couple of articles:

Mail from Excel with SendMail (VBA)

Sending mail from Excel with CDO

 

[rv02s]

Here are a couple of articles:

Mail from Excel with SendMail (VBA)

Sending mail from Excel with CDO

The frist link says I can not enter the body for my email which is no good as I have to enter a message. Sorry for asking but I am completeky new to coding, would I be able to use the CDO and use Outlook webmail?

Any exmaple is apprecaited in my code above to sj=how me what to change

 

[Andrew Poulsom]

Did you try the basic example of cdo in the second link I posted?

 

[rv02s]

I tried the example and it works but when I try to do this with my work email it sends the server could not authenticate. At work we are using Office365, and I think the smtp server is smtp.office365.com but I cant not get it working. do I have to be running this when I am in the office or can I try it from home. the gmail example worked fine but the office 365 does not run

 

[Andrew Poulsom]

I don't have enough knowledge of Outlook OWA to help you any further, sorry.

 

[Kyle123]

Sending SMTP mail via Office 365 exchange using CDO.Message and other applications | ClicData Blog

 

[DDonnachie]

Just came across this thread,
Tried Kyle's link, doesn't work but here is the same info.
https://clicdatacoder.wordpress.com...nge-using-cdo-message-and-other-applications/