Theva
asked on
Email activation failed.
Hi Experts,
I would like to request Experts Help. For some reason the attached script not activate outlook email even though I’ve the Outlook reference is active. Hope Experts can help me to rectify this problem. I’ve attached the workbook for your reference.
I would like to request Experts Help. For some reason the attached script not activate outlook email even though I’ve the Outlook reference is active. Hope Experts can help me to rectify this problem. I’ve attached the workbook for your reference.
Sub SendEmail()
'Uses early binding
'Requires a reference to the Outlook Object Library
Dim OutlookApp As Outlook.Application
Dim MItem As Outlook.MailItem
Dim olAppointment As Outlook.AppointmentItem
Dim cell As Range
Dim Subj As String
Dim EmailAddr As String
Dim Adate As Date
Dim Atime As String
Dim Msg2 As String
Dim SendStatus As String
'Create Outlook object
Set OutlookApp = New Outlook.Application
'Loop through the rows
For Each cell In Columns("H").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "*@*" Then
'Get the data
SendStatus = cell.Offset(0, 1)
If SendStatus <> "To be sent" Then GoTo NotThisOne
Subj = cell.Offset(0, -1).Value
EmailAddr = cell.Value
Adate = cell.Offset(0, -6).Value
Atime = cell.Offset(0, -5).Value
FileLocation = "C:\ApptFiles\OutlookAppointment.ics"
cell.Offset(0, 1).Value = "Sent"
Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Set olApp = New Outlook.Application
Set olApt = olApp.CreateItem(olAppointmentItem)
Msg2 = "New Task"
With olApt
.Start = Adate
.End = .Start + TimeValue("00:30:00")
.Subject = Msg2
.Location = " "
.Body = Msg
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = 2880
.ReminderSet = True
'.Display
'Save the iCalendar file in a known folder
.SaveAs "C:\ApptFiles\OutlookAppointment.ics", olICal
'Use Close to retain the new appointment within the Outlook Calendar, or Delete to delete it.
'Both options keep the just-created .ics file
'.Close False
.Delete
End With
Set MItem = OutlookApp.CreateItem(olMailItem)
With MItem
.To = EmailAddr
.Subject = Subj
.Body = Msg
.Attachments.Add (FileLocation)
.Send
'.Save 'to Drafts folder
End With
NotThisOne:
End If
Next
Set OutlookApp = Nothing
Set olApt = Nothing
Set olApp = Nothing
End Sub
EvenList.xls
ASKER
Hi Chris,
I need to activate Outlook references, if not it shows error at this line:
"OutlookApp As Outlook.Application"
The objective of having this script is to send calendar to recipient. Initially it works but I'm not what when its no longer works.
I need to activate Outlook references, if not it shows error at this line:
"OutlookApp As Outlook.Application"
The objective of having this script is to send calendar to recipient. Initially it works but I'm not what when its no longer works.
I have tried to 'tidy' it up in the hope it helps ... and moved it to late binding.
Chris
Chris
Sub SendEmail()
Dim olkApp As Object
Dim MItem As Object
Dim olkAppointment As Object
Dim cell As Range
Dim Subj As String
Dim EmailAddr As String
Dim Adate As Date
Dim Atime As String
Dim Msg As String
Dim Msg2 As String
Dim SendStatus As String
Dim FileLocation As String
Const olAppointmentItem As Integer = 1
Const olBusy As Integer = 2
Const olICal As Integer = 8
Const olMailItem As Integer = 0
'Loop through the rows
For Each cell In Columns("H").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "*@*" Then
'Get the data
SendStatus = cell.Offset(0, 1)
If SendStatus <> "To be sent" Then GoTo NotThisOne
Subj = cell.Offset(0, -1).Value
EmailAddr = cell.Value
Adate = cell.Offset(0, -6).Value
Atime = cell.Offset(0, -5).Value
FileLocation = "C:\ApptFiles\OutlookAppointment.ics"
cell.Offset(0, 1).Value = "Sent"
Set olkApp = CreateObject("outlook.application")
Set olkAppointment = olkApp.CreateItem(olAppointmentItem)
Msg2 = "New Task"
With olkAppointment
.Start = Adate
.End = .Start + TimeValue("00:30:00")
.subject = Msg2
.Location = " "
.body = Msg
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = 2880
.ReminderSet = True
'.Display
'Save the iCalendar file in a known folder
.SaveAs FileLocation, olICal
'Use Close to retain the new appointment within the Outlook Calendar, or Delete to delete it.
'Both options keep the just-created .ics file
'.Close False
.Delete
End With
Set MItem = olkApp.CreateItem(olMailItem)
With MItem
.To = EmailAddr
.subject = Subj
.body = Msg
.Attachments.Add (FileLocation)
.Send
'.Save 'to Drafts folder
End With
NotThisOne:
End If
Next
Set olkApp = Nothing
Set olkAppointment = Nothing
Set olkApp = Nothing
End Sub
ASKER
Hi,
Tried, when I click the command button its not responding at all.
Tried, when I click the command button its not responding at all.
I tried it before posting and as soon as I changed a couple of cells in column I it worked!
Chris
Chris
ASKER
Hi,
I've no idea why its not working in my PC. I've reused the original version that was used before introducing Column_I (email status). It works perfectly. I have attached the workbook and the script is in Module1. The module 2 is belongs to your refine version. Hope you can help me figure out what when wrong in Module2.
1EventList.xls
I've no idea why its not working in my PC. I've reused the original version that was used before introducing Column_I (email status). It works perfectly. I have attached the workbook and the script is in Module1. The module 2 is belongs to your refine version. Hope you can help me figure out what when wrong in Module2.
1EventList.xls
Send status isn't there any more. The code in module executes if there is an email in H whereas in module 2 it executes if there is an email in H ***AND***
the cell in column I is "To be sent"
I is blank therefore no mails will result.
Chris
the cell in column I is "To be sent"
I is blank therefore no mails will result.
Chris
ASKER
Hi Chris,
Thanks for the help. I need one more help. Is that possible when I send the email the event will be registered in my Outlook Calendar as well? Hope you will
Thanks for the help. I need one more help. Is that possible when I send the email the event will be registered in my Outlook Calendar as well? Hope you will
What / how do you want of the 'event' in the calendar?
Chris
Chris
ASKER
Hi,
Sorry if this request confusing you. What I need is to automatically update my Outlook Calendar based on information from Column B,C and G once click the Button "Email Send".
Sorry if this request confusing you. What I need is to automatically update my Outlook Calendar based on information from Column B,C and G once click the Button "Email Send".
One entry for all the rows? one entry per row.
No alarms or anything?
Chris
No alarms or anything?
Chris
ASKER
Hi Chris
One entry, which ever rows under status "To be Send" at Column_I will be automatically updated in Calender with Alarm (5mins before event).
One entry, which ever rows under status "To be Send" at Column_I will be automatically updated in Calender with Alarm (5mins before event).
Can you supply version of sheet and code is now working after all the last file did not have column I populated but teh code was checking it so before I try and add the code I would like a representative file to work with.
C hris
C hris
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Chris,
Thanks for the code. When I sent the email, received pop-up massage at Taskbar "Cannot save free/busy information" How to rectify this?
Thanks for the code. When I sent the email, received pop-up massage at Taskbar "Cannot save free/busy information" How to rectify this?
I have no idea, a quick trawl around suggests it affects a lot of people ... but in this case I doubt it needs to be a 'busy' appt so try commenting out:
.BusyStatus = olBusy
ie.
'.BusyStatus = olBusy
Chris
.BusyStatus = olBusy
ie.
'.BusyStatus = olBusy
Chris
ASKER
Hi Chris,
I've commented out line 47 and 83, yet showing the same result.
I've commented out line 47 and 83, yet showing the same result.
Hmmm, like I said there's a lot about it and I couldn't see anything conclusive so try the cleanfreebusy switch:
http://www.howto-outlook.com/howto/commandlineswitches.htm
Chris
http://www.howto-outlook.com/howto/commandlineswitches.htm
Chris
ASKER
Hi Chris,
I've checked the web page, not sure how to fix this bug.
I've checked the web page, not sure how to fix this bug.
ASKER
Hi Chris,
Thanks a lot for helping me to create this script.
Thanks a lot for helping me to create this script.
Basically restart outlook from the command line using the cleanfreebusy switch
Set OutlookApp = New Outlook.Application
Set olApp = New Outlook.Application
Set olApt = olApp.CreateItem(olAppoint
Set MItem = OutlookApp.CreateItem(olMa
Irrespective can you say why "attached script not activate outlook email "
Chris