Excel to Outlook Appointment

[dontasciime]

Hi Everyone,

I am new to this forum and I am really stuck! I have a macro in Excel that automatically emails specified users when any changes have been made to the worksheet.

Now they are asking me to create macro that generates an Outlook Appointment based on the due date in the same Excel sheet. I know that it can be done, but I am not sure how to!

These are the columns where the info is I need to include in the appointment. Ideally, they'd like to see the Subject as Column A, Column C and Column D.

Column A - Number
Column C - Item
Column D - Description
Column N - Due Date

Would anyone be able to help me or tell me where I can find a reference on how to write this code?

Thanks,
d*

 

[John_w]

Tried to use this too and got duplicate entries?

You shouldn't get duplicate appointments if you've set up your data exactly as described by the OP in post no. 1. A subject is constructed from the values of columns A, C and D like this:

subject = .Cells(row, "A").Value & " -- " & .Cells(row, "C").Value & " -- " & .Cells(row, "D").Value

and the code ensures that an appointment with such a subject occurs only once.

 

[dontasciime]

Yeah, I'm getting duplicates too. Not sure why, but it's driving me crazy! Maybe because I added in "--" in the subject?

 

[John_w]

The '--'s in the subject wouldn't cause duplicate, as the whole of the subject text is used to determine if the appointment exists or not. I'm using Excel 2003 and Outlook 2003 and it works for me. The following subroutine sets up some test data on Sheet3 and creates the appointments. Run it twice or more and see if it works for you.

Private Sub Test()

    Dim row As Long
    Dim subject As String
    
    With Sheet3
        .Activate
        For row = 2 To 6
            .Rows(row).ClearContents
            .Cells(row, "A").Resize(1, 4).Value = Array(row - 1, , "Item" & Chr(row + 63), "Desc" & row - 1)
            .Cells(row, "N").Value = Date + row
        Next
        
        For row = 2 To 6
            subject = .Cells(row, "A").Value & " -- " & .Cells(row, "C").Value & " -- " & .Cells(row, "D").Value
            Update_Appointment subject, .Cells(row, "N").Value
        Next
    End With
        
End Sub

If you're still having problems, I suggest you step through the code in the VB debugger and see if and why it is creating a new appointment every time.

 

[dontasciime]

This is crazy. I just can't seem to get this to work. Would I be able to send you my spreadsheet just to see if it works on your machine? I do not understand why this isn't working for me.

 

[John_w]

Does the test work? Try debugging the code, examining variables to understand how it works and if it isn't working, why.

 

[dailydud]

Hi John_w, your code worked perfectly. I know this is an old post but wanted to thank you for your help!

Dailydud