Macro to create Outlook appointment from excel file

[blandreth]

Let me start by saying I know nothing about writing code for macros and I do not have code to start with for what I'm looking for help with. The most I have done with macro coding is modify code for specific file paths or cell designations.

Now that I have that out of the way, I'm looking for code to run a macro that will pull a cell from an Excel file that contains a date and match that date in Outlook then create an all day appointment on this date with .5 day reminder. The subject line of the appointment would be the title of the Excel file that is shown in another cell in the form. Ultimately, I would like to run the macro so that it is run in the background without having to acknowledge anything in Outlook to create this appointment. I am currently using Office 2016. This Excel file would also be used by multiple people. I would want the macro to create the appointment in the users Outlook.

Any help would be greatly appreciated.

Thanks!

 

[lrobbo314]

How about this?

Sub CreateAppt()
On Error GoTo erHandle
Dim olApp As Object
Dim olItem As Object
Dim dt As Date


dt = Range("B13").Value
Set olApp = CreateObject("Outlook.Application")
Set olItem = olApp.CreateItem(1)


With olItem
    .Subject = Range("A1") 'Change to cell that has value for subject
    .Body = "Appointment Details" 'Can refer to another cell in sheet, i.e. range("A2")
    .ReminderMinutesBeforeStart = 60 '12 hours times 60 minutes
    .Start = dt + (12 / 24)
    .End = dt + (12 / 24)
    .AllDayEvent = False
    .Save
End With


Exit Sub


erHandle:
If Err.Number = 13 Then
    MsgBox "Active cell must containt a date", vbCritical
Else
    MsgBox "Error #" & Err.Number & vbCrLf & Err.Description
End If
End Sub

 

[blandreth]

That worked great!

Thank you so much...again!!

Have a great day.

 

[blandreth]

Here I go again. Need some more help. I have a starting point this time. This is a macro I use to update a log that captures specific information from a form I have. It will open the log find the last row or duplicate row if being updated and paste the new or updated information in the appropriate row on the log and then close the log. I'm now being asked to add to my form which will require different headers in the log. I would like to use the same log and add a different tab to dump this new data in on specific rows. How can I modify the code provided to dump the information in sheet 1 of the log if my choice is A or B and then dump information to sheet 2 if my choice is C. The information that is currently being dumped to sheet 1 is located in row A65:A67 and is named logdata. I would create a separate row for the choice C data to be dumped in sheet 2 of the log - lets say that will be A70:A75 and I would name this row of data "logdata2".

Sub GDLOGUPDATE()
Dim MainWkbk As Workbook 'one containing the form
Dim CCform As Worksheet

Set MainWkbk = ActiveWorkbook 'file containing the form that is already open
Set CCform = MainWkbk.Sheets("GENERAL DETAIL")
Application.ScreenUpdating = False


Range("A65").Select
RowID = ActiveCell.Value
Range("logdata").Select
Selection.copy

Workbooks.Open FileName:="XXXXXXXXXXXX\XXXX\log.xls"

Set NextWkbk = ActiveWorkbook 'log workbook
Set ccLog = NextWkbk.Sheets("Production Log")

'get the last row in the log if we need to add a new log
Dim LastRow As Long
LastRow = ccLog.UsedRange.Rows.Count

'Set variables to find existing log number
Dim rng As Range
Dim FindString As String

'Set the "FindString" variable to the current log number and search for it in column A
FindString = RowID
'find the current logID if it is present
Set rng = Cells.Find(What:=RowID, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

'If it finds the log, paste and replace the data
If Not rng Is Nothing Then
rng.Offset(0, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Transpose:=False
Application.CutCopyMode = False
Else
' If it does not find the log, add a row to the bottom
Sheets("Production Log").Range("A" & LastRow + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End If
'****************
'save and close the Log file
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True

'set active cell in form back to c8
Range("c8").Select

End Sub

I hope this makes sense. Please let me know if you have any questions.

 

[blandreth]

Sorry - I mis-stated my rows - listed them as columns. The data I am trying to copy and past in to the log is Row A65:K65 for selection A or B and Row A70:K70 for selection C from the form.

 

[blandreth]

I also noticed that there is some formulas in a separate tab (sheet 1) in my current log with the following information:

=lastincolumn('QC-CA-PA LOG'!A:A)

=TEXT(RIGHT(A1),"000")

=LEFT(A1,7)

They are listed in cells A1, A2 and A3 respectively. I think these are required for the log to look for the last row in the sheet to paste new information.

Also, if there is a better code to do what I'm looking to do, then I'm open. I don't favor the one provided, this is what I inherited and it worked so I never questioned it.

Ultimately, this is how I would like this macro to work. Press the macro button - macro determines selection in cell B9 (Quality, Standard or Safety) - copies data listed starting in A65:K65 if Quality or Standard are selected - opens log file - selects sheet 1 - finds last row starting with column A - past data - save and close form and return to a specified cell in my active open form. If Safety is selected - copies data listed in A70:K70 - opens log file - selects sheet 2 - finds last row starting with column A - past data - save and close form and return to a specified cell in my active open form. If this is an update to a line item on the log - sheet 1 or 2, I would want the macro to match the number in the log with the active file open and replace the updated contents on the same line and not paste a new line of information - save and close file to return to a specified in cell in the active open form.

If I can eliminate the need for the formulas posted above, even better!

I appreciate the feedback and help.

 

[blandreth]

Any suggestions?