Outlook VBA --> Setting a variable = to Excel cell value

[laxcat73]

Hi,

I have a macro I'm working on in Outlook that tries to attach a dynamic attachment to a set list of people.

Everything works great except when I decided I didn't want to go into the code to change the date every time we enter a new fiscal month. Being in retail, this never coincides with the calendar months. So, I created an excel workbook with dates going through January 2015 to look up off, and give me the fiscal month of whatever today's date is.

I'm having an error when using a function in the macro to go and set variable "currmonth1" to a value in the workbook. The error is:

Compile error:
User-defined type not defined. Error occurs at red, bolded text.

Function openExcel()

Dim xlApp As Object
Dim [B][COLOR=#ff0000]sourceWB As Workbook[/COLOR][/B]
Dim sourceWS As Worksheet

Set xlApp = CreateObject("Excel.Application")

With xlApp
.Visible = True
.EnableEvents = True
End With

strFile = "G:\Financial Planning\2012 Daily Sales\DailySalesMacro.xlsx"

Set sourceWB = Workbooks.Open(strFile, , False, , , , , , , True)
Set sourceWH = sourceWB.Worksheets("Sheet1")
sourceWB.Activate
Set currmonth1 = sourceWB.sourceWH.Cells(3, 7).Value
ActiveWorkbook.Close SaveChanges:=True

End Function

Any assistance is helpful. Not sure what I'm doing wrong.

Another way I was going to try is setting today's date in the code and doing a vlookup in VBA to return my information.

 

[lrobbo314]

While in the VBE, you need to go to Tools, References, and add the Microsoft Excel 14.0 Object Library as a reference.

 

[laxcat73]

Thanks, just did that.

Now I get error "Object Required" with bolded red below.

Function openExcel()

Dim xlApp As Object
Dim sourceWB As Workbook
Dim sourceWS As Worksheet
Dim currmonth1 As String

Set xlApp = CreateObject("Excel.Application")

With xlApp
.Visible = True
.EnableEvents = True
End With

strFile = "G:\Financial Planning\2012 Daily Sales\DailySalesMacro.xlsx"

Set sourceWB = Workbooks.Open(strFile, , False, , , , , , , True)
Set sourceWH = sourceWB.Worksheets("Sheet1")
sourceWB.Activate
Set [COLOR=#ff0000][B]currmonth1 =[/B][/COLOR] sourceWH.Cells(3, 7).Value
ActiveWorkbook.Close SaveChanges:=True

End Function

Currmonth1 is currently defined Public as it's used in another part of the module.

 

[RoryA]

Currmonth is a string, not an object, so remove the word 'Set' from in front of it.

 

[laxcat73]

Thanks!

Works like a charm now