how to save ranges into a running file...

[RompStar]

wow, so many things I am learning, ya manual seems great...

what about something like this:

Ok, in column B I have a validation drop-list, with many options...

When the document template is opened everyday by someone filling it
out, I would like to have the validation list for my range B11:B20 to
default to the first list option from the list... because after the
file is uploaded, I want them to save that template because the E1 date
needs to be stored, but when they reopen it the next day, I don't want
them to have to default to the first option in the list by hand,
because some departments have a lot of employees and it wastes time, so
if they reopen the template the next day, I want the E1 value stored
when they close the application to the template, but also when they
reopen it the next day for the list to default to the first option in
the list.

What do you think ?


any ideas ?

 

[Dave Peterson]

I'm not quite sure what you mean, but you could add something in the auto_open
(or workbook_open) procedure to make that cell whatever you want.

with worksheets("sheet1").range("e1")
.value = "type the value you want here"
'or
.clearcontents 'to reset to empty
end with

 

[RompStar]

thanks for all your help, prbably going to need it in the future :- )

lol

thanks again.

 

[Dave Peterson]

Glad everything is working.

See you in your next thread <bg>.

 

[RompStar]

haha, I think I need your help again, also new things to learn ;- )

ok, this script looks in the active sheet and takes the emails out of
column M and generates emails, right now it's grabbing the header
files, but I want a simple static template to be put into the body of
the message, so I think the last part needs to be changed, the Function
RangetoHTML2()..

All I want is a simple template to start off in the body of the
message, like

start body ---

Hello,

blalblalalalalalalalalblblblalblblblalbaslablbalal blablab la
dasdasdasdasdasdasdasdasdasdasdasdasd.


Have a greate blablabla...

Signature...

end body --------


--------------- how the script looks now....

Option Explicit

Dim Nsh As Worksheet

Sub Send_Row()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim cell As Range
Dim rng As Range
Dim Ash As Worksheet

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set Ash = ActiveSheet
Set Nsh = Worksheets.Add
Ash.Activate

On Error GoTo cleanup
For Each cell In
Ash.Columns("M").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" Then
Ash.Range("A1:O100").AutoFilter Field:=2,
Criteria1:=cell.Value
With Ash.AutoFilter.Range
On Error Resume Next
Set rng = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
rng.Copy Nsh.Cells(1)
Nsh.Columns.AutoFit
Ash.AutoFilterMode = False

Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = cell.Value
.Subject = "PC Recycle pickup request"
.HTMLBody = RangetoHTML2
.Display 'Or use Display for testing. .Send for real
End With
Set OutMail = Nothing
Nsh.Cells.Clear
End If
Next cell

cleanup:
Application.DisplayAlerts = False
Nsh.Delete
Application.DisplayAlerts = True
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub


Function RangetoHTML2()
Dim fso As Object
Dim ts As Object
Dim TempFile As String
TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=Nsh.Name, _
Source:=Nsh.UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML2 = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function

I understand most of this code, but not everything, any idea how to
change this ? considering that I want a simple template for the BODY,
just text ?

Also I noticed that if there were 20 rows, with the email address being
in column M of each row, it generates, a new email message for each
one, I would prefer to have it all in a single email and just space the
aliases

email; email2; email3

any ideas ?

 

[Dave Peterson]

You may want to post in a new thread. I don't use Outlook and don't think I
could help.

 

[RompStar]

Is ok :- )

I bought this cool book called Excel VBA Programming for Dummies, wow,
great way of explaining things, I was reading this other book and it
wasn't explain things well at all, but now that I read this book,
everything is starting to make sense, I love the For Dummies Series as
the first step.

 

[Dave Peterson]

Debra Dalgleish has a list of books at:
http://www.contextures.com/xlbooks.html

You may want to print a copy and take it with you when you visit your local
bookstore/library.