How to Activate keyboard function from ms-access

[sunilvedula]

HI all,

I want to trigger macro express when a key is pressed in ms-access database. i have developed a database in ms- access which multiple users use. Now when they complete a work they need to send an email to business are confirming the job is done with a formatted excel sheet to which a reply is sought from the business area who will attach some images and send it back to us.

With access i am able to send mail but cannot format the excel sheet the way i want. i have designed a macro to do the desing part so that it goes to the mail and pastes the data in exce in rich text format so that the business are people can drop images beside the data and send it back to us.

to make this possible i should be able to trigger the macro express but that i find it difficult. i can try opening the macro file with sql code but how do i make it trigger?

Is there a way to activate at least a function key from ms- access or is there a way i can tell in my code to paste the data from excel sheet into mail in rich text format.

any ideas please put forward as i need to submit my presentation ASAP.

 

[3dman]

Please post your code.

 

[sunilvedula]

which code? the database code is huge. what i want is there a way we can trigger any function key on the keyboard like F2 OR F3 anything... else is there a way instead of attaching excel document i want it to paste the content of hte excel is columns

 

[3dman]

Sorry I meant the macro one.

 

[sunilvedula]

hi, me too sorry. I designed the macro in macro express. Do you want me to post that code? i assume that would make no difference. That macro gets triggered with a hotkey called F2 or anything. I have also found a way to send email throug access with required data in it but i have few problems. I am attaching the code below.

'Email Addresses
Dim rst As DAO.Recordset
Dim strEmailAddresses As String
Dim notesdb As Object
Dim notesdoc As Object
Dim notesrtf As Object
Dim notessession As Object
Set notessession = CreateObject("Notes.Notessession")
Set notesdb = notessession.getdatabase("", "")
Call notesdb.openmail
VArea = DLookup("[Area]", "qStatusCheck")
Set rst = CurrentDb.OpenRecordset("qEmailArea")
Do Until rst.EOF
strEmailAddresses = strEmailAddresses & rst("Email")& ", "
rst.MoveNext
Loop
'Text in the email
Dim rst2 As DAO.Recordset
Dim strNames As String
Set rst2 = CurrentDb.OpenRecordset("qMailCheck")
Do Until rst2.EOF
strNames = strNames & vbCrLf & rst2("Pan") & ", " & rst2("Switch") & ","
rst2.MoveNext
Loop
Rem make new mail message
Set notesdoc = notesdb.createdocument
Call notesdoc.replaceitemvalue("Sendto", strEmailAddresses)
Call notesdoc.replaceitemvalue("Subject", "TESTING AUTO EMAIL")
Set notesrtf = notesdoc.createrichtextitem("body")
Call notesrtf.appendtext("The Case list where no response was received and cases being closed are")
Call notesrtf.addnewline(2)
'Rem attach Error Report doc
's = ActiveDocument.Path + "\" + ActiveDocument.Name
's = "J:\OPS DESK\Sunil\Development Projects\HBME\Blockcard.XLS"
'Call notesrtf.embedObject(1454, "", "J:\OPS DESK\Sunil\Development Projects\HBME\Blockcard.xls", "Blockcard.xls") '*** working to attach report
Call notesrtf.appendtext(strNames) '*** to write content of the email
'Rem send message
Call notesdoc.Send(False)
Set notessession = Nothing
strEmailAddresses = ""

The problem i face is it is not able to send to multiple receipents. what do i do. Second thing is the data that gets pasted is raw but i would like it to be a bit formatted like coloums and rows. Can you help?

 

[sunilvedula]

hi all,

i figured out how to send emails to mulitple receipents but i would like to know why it is not sending out the mail. it stays in the mail and we have to click manually to send it. i am trying to send it through lotus notes. please find attached the code i have used.

Dim rst As DAO.Recordset
Dim strEmailAddresses As String
Dim strccemail As String
Dim notesdoc As Object
'VArea = DLookup("[Area]", "qStatusCheck")
'Set rst = CurrentDb.OpenRecordset("qEmailArea")
Set rst = CurrentDb.OpenRecordset("TblUserEmail")
Do Until rst.EOF
strEmailAddresses = strEmailAddresses & rst("Email") & ","
rst.MoveNext
Loop
'Text in the email
Dim rst2 As DAO.Recordset
Dim strNames As String
strNames = "Please find attached details of the item for which we did not receive any supporting doc"
Set rst2 = CurrentDb.OpenRecordset("qMailChecktest")
Do Until rst2.EOF
strNames = strNames & vbCrLf & "AREA" & " " & " CARDNUMBER" & " " & "ORGDT" & " " & "RESPCODE" & " " & "ATM" & " " & "TIME" & " " & "STLMTAMT" & " " & "SWITCH"
strNames = strNames & vbCrLf & rst2("AREA") & ", " & rst2("Pan") & ", " & rst2("Orgdt") & ", " & rst2("Respcode") & ", " & rst2("ATM") & ", " & rst2("Time") & ", " & rst2("STLMTAMT") & ", " & rst2("Switch")
rst2.MoveNext
Loop
' send email
DoCmd.SendObject acSendQuery, "qMailChecktest", xls, strEmailAddresses, , , "No Doc Received Report", strNames, False
strEmailAddresses = ""
End Sub

 

[gemma-the-husky]

loook at autokey macros

basically you set any key combination of Ctrl+Key to fire up a function.

note that this new behaviour overwrites existing Ctrl+Key behaviour, but there are a number of unassigned keys that can by used. or if you never use Ctrl-P to print (Ctrl-P fires up printer dialog as standard , then use this one)

Note that Autokey macros are one of the few things you cannot replicate in vba.