Consulting

Results 1 to 3 of 3

Thread: Excel data to create meeting invitation in Outlook...Almost there (I hope)

  1. #1

    Excel data to create meeting invitation in Outlook...Almost there (I hope)

    Hi all, I know this has been tackled in various forums but I just can't quite find the answer I need. I'm fairly new to this so struggling a bit...

    I'm trying to extract data from a sheet to create meeting requests. Each line on the spreadsheet will set up a new meeting invitation.

    After playing around a lot, I can get it to generate meeting requests if the data is on the same sheet as the code. However, I need the code to be on a different sheet to the table itself. I also can't get it to use email addresses within the sheet....Only when I specify them in the actual code.

    Here is the data I'm using: The worksheet with this table is called 'Curtailments'

    Site name Unit ID Agent Entity (MW) Start time Start date Cease time Cease Date
    Batsworthy Cross ODFM44-01 TestCo1 Entity1 100 10:00 05/05/2020 16:00 05/05/2020
    Denzel Downs ODFM45-01 TestCo1 Entity1 200 11:00 05/05/2020 15:00 05/05/2020
    Forss ODFM42-01 TestCo1 Entity1 0 10:00 05/05/2020 16:00 05/05/2020
    Little Raith ODFM80-01 TestCo1 Entity1 0 11:00 05/05/2020 15:00 05/05/2020


    Below is the code that's on a different sheet:

    Dim OLook As Outlook.Application
    Set OLook = New Outlook.Application
    Dim sh As Worksheet
    Set sh = sheets("Curtailments")
    Dim Oapt As Outlook.AppointmentItem
    Dim r As Long
    Dim mylist As String
    On Error Resume Next




    Set Oapt = OLook.CreateItem(olAppointmentItem)
    On Error GoTo 0
    r = 2 ' first row with data
    Oapt.MeetingStatus = olMeeting


    With Oapt
    ' read appointment values from the worksheet
    On Error Resume Next
    Oapt.Recipients.Add ("Need to get addresses from curtailments sheet")
    .Start = sh.[Cells(r, 7).Value + Cells(r, 6).Value]
    .End = Cells(r, 3).Value + Cells(r, 4).Value
    .Subject = Cells(r, 5).Value
    .Location = Cells(r, 6).Value
    .Body = "testing"
    .ReminderSet = Cells(r, 7).Value
    On Error GoTo 0
    .Display
    '.Save ' saves the new appointment to the default folder


    End With
    Set Oapt = Nothing
    Set OLook = Nothing


    End Sub

    As you can see I've tried but failed miserably so any help would be very much appreciated. Thank you in advance.

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Best way to get help is to put together a workbook with enough data to show the issue and include your macros

    Not understanding ...

    Below is the code that's on a different sheet:
    Do you mean that it's in a standard module or in an ActiveX button?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Move the code from the sheet in question to a new module, from which you can address any sheet in the workbook.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •