Pass Variable from procedure to function

[AirgasRob]

First thanks for all the help so far, I am a step closer to figuring out this
procedure.

What I am not quite sure how to do is pass the UDC_Buyer field to the
function, and have it used
as the send to.


Procedure for exporting to excel

DoCmd.Hourglass True
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1

Dim mySQL As String
mySQL = "SELECT Loc, Item, HoldComment, UDC_Buyer, PrevM01, PrevM02,
PrevM03, PrevM04, PrevM05, PrevM06, UDC_SkuCost FROM QryTblMaintbl WHERE
TblMaintbl.Loc = """ & [Forms]![FrmMain]![SelectLoc] & """"

myRecordSet.Open mySQL

Dim mysheetpath As String
mysheetpath = "C:\TransferStation\MarketIntelligence.xls"

Dim xl As Excel.Application
Dim xlbook As Excel.workbook
Dim xlsheet As Excel.worksheet

Set xl = CreateObject("excel.application")
Set xlbook = GetObject(mysheetpath)

xlbook.Windows(1).Visible = True

Set xlsheet = xlbook.worksheets(1)

xlsheet.range("a3").CopyFromRecordset myRecordSet

myRecordSet.Close

On Error Resume Next:

xlbook.SaveAs ("C:\TransferStation\ExportExcel\MarketIntelligence.xls")


xlsheet.Application.Quit

Set xl = Nothing
Set xlbook = Nothing
Set xlsheet = Nothing

DoCmd.SetWarnings False
DoCmd.OpenQuery "QryUpdateSent", acNormal, acEdit
DoCmd.SetWarnings True

DoCmd.Hourglass False

DoCmd.Close acForm, "FrmMain"

Call sendMessage("C:\TransferStation\ExportExcel\MarketIntelligence2.xls")

DoCmd.OpenForm "FrmMain", acNormal, "", "", , acNormal

Function for sending the outlook message

Function sendMessage(Optional AttachmentPath)

Dim olookApp As Outlook.Application
Dim olookMsg As Outlook.MailItem
Dim olookRecipient As Outlook.Recipient
Dim olookAttach As Outlook.Attachment

' create the Outlook session.
Set olookApp = CreateObject("Outlook.Application")

' create the message.
Set olookMsg = olookApp.CreateItem(olMailItem)

With olookMsg

' add the To recipient(s) to the message.
Set olookRecipient = .Recipients.Add("Someone")
olookRecipient.Type = olTo
' set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft Outlook"
.Body = "I am so close to figuring this out." & vbCrLf & vbCrLf
.Importance = olImportanceHigh 'High importance

' add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set olookAttach = .Attachments.Add(AttachmentPath)
End If

' resolve each Recipient's name
For Each olookRecipient In .Recipients
olookRecipient.Resolve
If Not olookRecipient.Resolve Then
olookMsg.Display ' display any names that can't be resolved
End If
Next
.Display

End With
Set olookMsg = Nothing
Set olookApp = Nothing

End Function

 

[Stefan Hoffmann]

hi Rob,

What I am not quite sure how to do is pass the UDC_Buyer field to the
function, and have it used
as the send to.

Procedure for exporting to excel
[..]
mySQL = "SELECT Loc, Item, HoldComment, UDC_Buyer, PrevM01, PrevM02,
PrevM03, PrevM04, PrevM05, PrevM06, UDC_SkuCost FROM QryTblMaintbl WHERE
TblMaintbl.Loc = """ & [Forms]![FrmMain]![SelectLoc] & """"

There is no other occurence of UDC_Buyer. So what do you like to do with
it? When or where in your code will you need it?

Function sendMessage(Optional AttachmentPath)

May be here?

Function sendMessage(Buyer As String, Optional AttachmentPath)

?


mfG
--> stefan <--

 

[AirgasRob]

Thank you for the suggestions I will play around with them and see if I can
get it right.

Below is where I want the UDC_Buyer field to pass to.

' add the To recipient(s) to the message.
Set olookRecipient = .Recipients.Add("[UDC_Buyer]")
olookRecipient.Type = olTo

hi Rob,

What I am not quite sure how to do is pass the UDC_Buyer field to the
function, and have it used
as the send to.

Procedure for exporting to excel
[..]
mySQL = "SELECT Loc, Item, HoldComment, UDC_Buyer, PrevM01, PrevM02,
PrevM03, PrevM04, PrevM05, PrevM06, UDC_SkuCost FROM QryTblMaintbl WHERE
TblMaintbl.Loc = """ & [Forms]![FrmMain]![SelectLoc] & """"

There is no other occurence of UDC_Buyer. So what do you like to do with
it? When or where in your code will you need it?

Function sendMessage(Optional AttachmentPath)

May be here?

Function sendMessage(Buyer As String, Optional AttachmentPath)

?


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

Below is where I want the UDC_Buyer field to pass to.

' add the To recipient(s) to the message.
Set olookRecipient = .Recipients.Add("[UDC_Buyer]")
olookRecipient.Type = olTo

So use

Call sendMessage(myRecordSet![UDC_Buyer], "yourExcel.xls")

before closing your recordset.



mfG
--> stefan <--

 

[AirgasRob]

Stefan thanks for all your help so far. I made the changes you suggested but
for some reason nothing is happening when I Call SendMessage Function. I get
the spreadsheet but no email is being generated. I am going to assume that my
syntax is incorrect for passing the varialbe through to the function. I will
post my entire procedure and function below and hope you can see my mistake.
Again thanks for advice so far!

Private Sub Command3_Click()
DoCmd.Hourglass True
If Dir("C:\TransferStation\ExportExcel\MarketIntelligence2.xls") <> ""
Then
Kill "C:\TransferStation\ExportExcel\MarketIntelligence2.xls"

End If
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim myrecordset As New ADODB.Recordset
myrecordset.ActiveConnection = cnn1

Dim mySQL As String
mySQL = "SELECT Loc, Item, HoldComment, UDC_Buyer, PrevM01, PrevM02,
PrevM03, PrevM04, PrevM05, PrevM06, UDC_SkuCost FROM QryTblMaintbl WHERE
TblMaintbl.Loc = """ & [Forms]![FrmMain]![SelectLoc] & """"

myrecordset.Open mySQL

Dim mysheetpath As String
mysheetpath = "C:\TransferStation\MarketIntelligence.xls"

Dim xl As Excel.Application
Dim xlbook As Excel.workbook
Dim xlsheet As Excel.worksheet

Set xl = CreateObject("excel.application")
Set xlbook = GetObject(mysheetpath)

xlbook.Windows(1).Visible = True

Set xlsheet = xlbook.worksheets(1)

xlsheet.range("a3").CopyFromRecordset myrecordset

myrecordset.Close

On Error Resume Next:

xlbook.SaveAs ("C:\TransferStation\ExportExcel\MarketIntelligence2.xls")

xlsheet.Application.Quit

Set xl = Nothing
Set xlbook = Nothing
Set xlsheet = Nothing

DoCmd.SetWarnings False
DoCmd.OpenQuery "QryUpdateSent", acNormal, acEdit
DoCmd.SetWarnings True

DoCmd.Hourglass False

DoCmd.Close acForm, "FrmMain"

DoCmd.OpenForm "FrmMain", acNormal, "", "", , acNormal

Call sendMessage(myrecordset![udc_buyer],
"C:\TransferStation\ExportExcel\MarketIntelligence2.xls")

End Sub


Option Explicit
Function sendMessage(buyer As String, Optional AttachmentPath)

Dim olookApp As Outlook.Application
Dim olookMsg As Outlook.MailItem
Dim olookRecipient As Outlook.Recipient
Dim olookAttach As Outlook.Attachment

' create the Outlook session.
Set olookApp = CreateObject("Outlook.Application")

' create the message.
Set olookMsg = olookApp.CreateItem(olMailItem)

With olookMsg

' add the To recipient(s) to the message.

' add the To recipient(s) to the message.
Set olookRecipient = .Recipients.Add(buyer)
olookRecipient.Type = olTo

' set the Subject, Body, and Importance of the message.
.Subject = "Market Intelligence"
.Body = "Attached is the Market Intelligence Spread Sheet." & vbCrLf &
vbCrLf
.Importance = olImportanceHigh 'High importance

' add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set olookAttach = .Attachments.Add(AttachmentPath)
End If

' resolve each Recipient's name
For Each olookRecipient In .Recipients
olookRecipient.Resolve
If Not olookRecipient.Resolve Then
olookMsg.Display ' display any names that can't be resolved
End If
Next
.Display

End With
Set olookMsg = Nothing
Set olookApp = Nothing

End Function

 

[Stefan Hoffmann]

hi,

Private Sub Command3_Click()

Call sendMessage(myrecordset![udc_buyer],
"C:\TransferStation\ExportExcel\MarketIntelligence2.xls")

You can't use myrecordset![udc_buyer] in this place, as you have closed
the recordset before.

End Sub

Function sendMessage(buyer As String, Optional AttachmentPath)

.Display

The code so far looks good, but you only display the message, use .Send
for sending it.

End Function

mfG
--> stefan <--

 

[AirgasRob]

Stefen,

On the slim chance you check this message!

Neither my VBA skills nor my book I am using have been able to help me.
Could you please show me how to store the UDC_Buyer field so that I am able
to pass it on to the function?

 

[Stefan Hoffmann]

hi Rob,

Neither my VBA skills nor my book I am using have been able to help me.
Could you please show me how to store the UDC_Buyer field so that I am able
to pass it on to the function?

Private Sub Command3_Click()
' declare a variable to hold it:
Dim strBuyer As String

DoCmd.Hourglass True
If Dir("C:\TransferStation\ExportExcel\MarketIntelligence2.xls") <> ""
Then
Kill "C:\TransferStation\ExportExcel\MarketIntelligence2.xls"

End If
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim myrecordset As New ADODB.Recordset
myrecordset.ActiveConnection = cnn1

Dim mySQL As String
mySQL = "SELECT Loc, Item, HoldComment, UDC_Buyer, PrevM01, PrevM02,
PrevM03, PrevM04, PrevM05, PrevM06, UDC_SkuCost FROM QryTblMaintbl WHERE
TblMaintbl.Loc = """ & [Forms]![FrmMain]![SelectLoc] & """"

myrecordset.Open mySQL

' store it:
strBuyer = myrecordset![UDC_Buyer]

Dim mysheetpath As String
mysheetpath = "C:\TransferStation\MarketIntelligence.xls"

Dim xl As Excel.Application
Dim xlbook As Excel.workbook
Dim xlsheet As Excel.worksheet

Set xl = CreateObject("excel.application")
Set xlbook = GetObject(mysheetpath)

xlbook.Windows(1).Visible = True

Set xlsheet = xlbook.worksheets(1)

xlsheet.range("a3").CopyFromRecordset myrecordset

myrecordset.Close

On Error Resume Next:

xlbook.SaveAs
("C:\TransferStation\ExportExcel\MarketIntelligence2.xls")

xlsheet.Application.Quit

Set xl = Nothing
Set xlbook = Nothing
Set xlsheet = Nothing

DoCmd.SetWarnings False
DoCmd.OpenQuery "QryUpdateSent", acNormal, acEdit
DoCmd.SetWarnings True

DoCmd.Hourglass False

DoCmd.Close acForm, "FrmMain"

DoCmd.OpenForm "FrmMain", acNormal, "", "", , acNormal

' use it:
Call sendMessage(strBuyer, _
"C:\TransferStation\ExportExcel\MarketIntelligence2.xls")

End Sub


Option Explicit

Function sendMessage(buyer As String, Optional AttachmentPath)
' ..
Set olookRecipient = .Recipients.Add(buyer)
' this should set your buyer
End Function



mfG
--> stefan <--

 

[AirgasRob]

Thank you very much, and by the way you just cleared up a lot of confussion I
had on other parts as well. I makes much more sense to me now!