Import Excel sheet data into outlook using macro

Hi friends,

Here I am going to put the script which is actually run by a macro in Excel sheet which will put the excel sheet data into the Microsoft outlook.

There are two macros required which will put a button into the excel sheet. I have named the sheet as “Data” [Mandetory].

Below is the code to put button into the excel sheet.

Sub AddButton()
Set btn = ActiveSheet.Buttons.Add(0, 90, 100, 25)
'ActiveSheet.Buttons.Add(x,y,length,width)
btn.OnAction = "AddAppointment"
btn.Characters.Text = "Run Outlook"End Sub

The second macro is the macro which will actually put the following data (in the figure) into the outlook

Private Sub AddAppointment()
Dim objOL 'As Outlook.Application
Dim objAppt 'As Outlook.AppointmentItem
Const olAppointmentItem = 1
Const olMeeting = 1
Const olFree = 0


MySubject = Worksheets("Data").Range("A2").Value
MyStartDatetime = Worksheets("Data").Range("A3").Value
MyDuration = Worksheets("Data").Range("A4").Value
MyLocation = Worksheets("Data").Range("A5").Value
MyBody = Worksheets("Data").Range("A6").Value
MyDelay = Worksheets("Data").Range("A7").Value


Set objOL = CreateObject("Outlook.Application")
Set objAppt = objOL.CreateItem(olAppointmentItem)

objAppt.Subject = MySubject
objAppt.Start = MyStartDatetime
objAppt.Duration = MyDuration
objAppt.Location = MyLocation
objAppt.Body = MyBody
objAppt.ReminderMinutesBeforeStart = MyDelay
objAppt.BusyStatus = olFree
objAppt.Save
Set objAppt = Nothing
Set objOL = Nothing
End Sub