Need help using Excel to create Outlook reminders

[furstukin]

I have found several codes to create a calendar reminder and I have worked out one that should be perfect for me, but I have one problem.

The body of the calendar event needs to be everything in cell A1:I50 which includes a small logo picture. I have declared this as SBORDER, but the problem I have is what tpye of variable can I use for this? I have tried Object but it says I have not defined it using a "with" statement and I have tried Range, but I get the same error. I also tried String but it says that I can't use a range of cells as a String.

here is my code

Function AddOutLookAppt()
Dim appOutLook As Outlook.Application
Dim ApptOutLook As Outlook.AppointmentItem
Dim STARDATE As Date
Dim ENDDATE As Date
Dim SBORDER As String
STARDATE = Range("E15").Value
ENDDATE = Range("E15").Value
SBORDER = Range("A1:I50").Value
Set appOutLook = CreateObject("Outlook.Application")
Set ApptOutLook = appOutLook.CreateItem(olAppointmentItem)
With ApptOutLook
.Start = STARTDATE
.End = ENDDATE
.AllDayEvent = True
.Subject = "Order Due in Two Days"
.Location = ""
.Body = SBORDER
.BusyStatus = olFree
.ReminderSet = True
.ReminderMinutesBeforeStart = 0
.Save
End With
End Function

Any help on this would be greatly appreciated as always.

Also a very helpful tip I found on this forum to anyone trying to use this function. You need to go to the VBA editor under tools and references then enable the Microsoft Outlook 12.0 Object Library or you will get a user type not defined error.

 

[furstukin]

I found one useful macro that allosws me to select my entire range and place it on the clipboard as a picture.

Range("A1:I50").CopyPicture Appearance:=xlScreen, Format:=xlPicture

But for the life of me I can not figure out how to paste that into my message.

I have tried all sorts of stuff including this.

Option Explicit
Sub AddOutLookAppt()
Dim appOutLook As Outlook.Application
Dim ApptOutLook As Outlook.AppointmentItem
Dim STARTDATE As String
Dim ENDDATE As String
Dim SBORDER As String
Application.ScreenUpdating = True
ThisWorkbook.Activate
Sheets("Existing Customer SB").Activate
STARTDATE = Range("O1").Value
ENDDATE = Range("O2").Value
Range("A1:I50").CopyPicture Appearance:=xlScreen, Format:=xlPicture
Sheets("Sheet1").Activate
Range("A1").Select
ActiveSheet.Paste
SBORDER = Worksheets("Sheet1").Shapes("Picture 1")
Set appOutLook = CreateObject("Outlook.Application")
Set ApptOutLook = appOutLook.CreateItem(olAppointmentItem)
With ApptOutLook
.Start = STARTDATE
.End = ENDDATE
.AllDayEvent = True
.Subject = "Order Due in Two Days"
.Location = ""
.Body = SBORDER
.BusyStatus = olFree
.ReminderSet = True
.ReminderMinutesBeforeStart = 0
.Save
End With
End Sub

But this just tels me it does not support this property or method when trying to declare the SBORDER line as the sheet1 picture.

Someone please help me this is driving me nutz.

 

[John_w]

You can't assign a shape ("Picture 1") to a string variable, only to a Shape object. You can only assign a string to the appointment's Body property, so SBORDER must be a string. Try changing the .Save to .Display and calling SendKeys to paste (Ctrl+v) the clipboard.