PDA

View Full Version : Sleeper: User defined range and sheet



austenr
06-10-2005, 12:51 PM
I have a macro that sends an email from EXCEL. Right now the range and sheet are hard coded into the sub. If there a way to let the user select the range (with the mouse) and sheet(s) that they want to be included in the email then turn it into a zip file as an attachment before sending? Thanks I am including the code as a zip file because it is large. Thanks

austenr
06-10-2005, 02:11 PM
Here is what I have so far:


Option Explicit

Public Sub MaillWorkbook()
Dim TextBox1 As String
Dim TextBox2 As String
Dim EmailSubject As String 'Email Subject Line
Dim UserEmail As String 'The email address that is being sent to
Dim Maildb As Object 'The mail database
Dim UserName As String 'The current users notes name
Dim MailDbName As String 'The current users notes mail database name
Dim MailDoc As Object 'The mail document itself
Dim AttachME As Object 'The attachment richtextfile object
Dim Session As Object 'The notes session
Dim EmbedObj As Object 'The embedded object (Attachment)
Dim Subject As String 'The subject string
Dim Attachment As String 'The path to the attachemnt string
Dim Recipient As String 'The Recipient string (or you could use the list)
Dim Recip(10) As Variant 'The Recipient list
Dim BodyText As String 'The body text
Dim SaveIt As Boolean 'Save to sent mail
Dim WasOpen As Integer 'Checking to see if the Mail DB was already
'open to determine if session should be
'closed (0) or left alone (1)
Dim ClipBoard As DataObject 'Data object for getting text from clipboard
UserForm1.Show
GetData
'Recipient = TextBox1
'Subject = TextBox2
'UserEmail = InputBox(PROMPT:="Please enter recipient email address")
'EmailSubject = InputBox(PROMPT:="Please enter subject line")
'Subject = EmailSubject
'Recipient = UserEmail 'Copying it to Clipboard
CloseForm
Sheets("Sheet1").Select
Range("A1:i26").Select
Selection.Copy
Set ClipBoard = New DataObject
ClipBoard.GetFromClipboard
SaveIt = True
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = Session.GETDATABASE("", MailDbName)
If Maildb.IsOpen = True Then
WasOpen = 1 'Already open for mail
Else
WasOpen = 0
Maildb.OPENMAIL 'This will prompt you for password
End If
Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = "Memo"
MailDoc.sendto = Recipient 'Or use Racip(10) for multiple
MailDoc.Subject = Subject
MailDoc.body = ClipBoard.GetText(1)
MailDoc.SAVEMESSAGEONSEND = SaveIt
If Attachment <> "" Then
Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment")
MailDoc.CREATERICHTEXTITEM ("Attachment")
End If
MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
MailDoc.SEND 0, Recipient
'Clean Up
Range("A1").Select
Application.CutCopyMode = False
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set EmbedObj = Nothing
If WasOpen = 1 Then
Set Session = Nothing
ElseIf WasOpen = 0 Then
Session.Close
Set Session = Nothing
End If
MsgBox "The Email was sent successfully!!", vbOKOnly
End Sub

Sub GetData()
Dim Recipient As String
Dim Subject As String
With Me
.TextBox1 = Recipient
.TextBox2 = Subject
End With
End Sub

Private Sub UserForm_Click()
Unload Me
End Sub


Still need to find a way to get user input (have the form but is not working correctly) and a way for the user to define the print range and sheet(s) to email

BlueCactus
06-10-2005, 05:29 PM
If there a way to let the user select the range (with the mouse) and sheet(s) that they want to be included in the email then turn it into a zip file as an attachment before sending?

You could try a RefEdit control. Works pretty much like a TextBox. This code fragment might give you a handle on its use (the VBA docs are pretty p*ss poor on this subject)


If RefEdit1.Text = Empty Then
MsgBox "Nothing Selected"
Else
Range(RefEdit1.Text).Select
End If